Two Spreadsheets

tvinci
Participant V

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

0 15 470
15 REPLIES 15

Jonathon
Participant V

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

2X_a_acba93f67f775c61ae70e575d73703fce5a2a95a.png

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:

Thank you @Jonathon

I tried that and this is what happened.

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.

2X_d_d6ebe95710462c067e7d614cd906756f696c4311.png
2X_8_84d139a92d0584a93a3c03e943e6f5e65ffcb87d.png

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!

Top Labels in this Space