Hello AppSheet
I have two spreadsheets, both with key columns. They have a parent-child relationship. The “key” column in the clipboard spreadsheet becomes a value in the “order” column in the delineatedLocations spreadsheet. All rows with the same value in the “order” column in the delineatedLocations spreadsheet correspond to each other in a group within the clipboard spreadsheet.
I’m trying to write a SELECT() statement that groups the rows according to whether or not they have the same value in the “order” column. Something like
SELECT(delineatedLocations[key],value in order
column=value in key column in clipboards table)
How do I go about this?
Thank you.
Tiffany
Hi Tiffany; in this case the delineatedLocations
table contains the child records and the clipboard
table contains the parent records.
Have you set the order column in the delineatedLocations
spreadsheet to type REF
, pointed at the clipboard
table?
Yes
Where is this SELECT() statement going? Is it a virtual column in the clipboard table? In this case it would be:
SELECT(delineatedLocations[key],
[order]=[_THISROW].[key]
)
It’s a virtual column in a third table, a routes table, but I will try what you suggested. Thanks.
Routes has a relationship to Clipboard. The key value in Routes becomes the customer value in Clipboard
Your Clipboard
table should have an auto-generated virtual column of Related delineatedLocations
. This is a list of all delineatedLocations that reference that clipboard row.
Similarly, your Routes
table should have an auto-generated virtual column of Related Clipboard
records.
To access the list of delineatedLocations in your Routes table, you can use:
[Related Clipboard].[Related delineatedLocations]
Further reading:
I checked and those columns do exist in the tables like you said they would.
Right - you will need to make sure the spelling is completely correct. For example, in the screenshot you shared above it appears your table is actually called DELINEATEDLOCATIONSV7
, therefore your column would likely be Related DELINEATEDLOCATIONSV7
.
Also, you do not need to wrap it in a SELECT() statement. Simply put [Related Clipboard].[Related delineatedLocations]
with correct spelling.
I have different versions of the app, so the spelling is actually correct. I double checked the spelling. I don’t know what’s going on.
Can you share editor access with jonathonsinclair@gmail.com ? I can troubleshoot for you this way.
Yes, thank you.
Done
Hey tvinci; I made a virtual column named ‘test’ in the Routes table that should work:
Apologies, for a reverse reference we exclude the ‘.’ separator - I was including it in my previous instructions.
Thank you very much!
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |