Linking a child table with another child table

[Rafael_ANEIC-PY]

Hello friends, i’m making my first app using the relational database schema and i have a question, im making a sales app with this structure:

I want to know if i can reference the Sites child table from the Transactions child table in order to pick or add a site in the same way i could add a site from the client table using references, im aware that i could use the select() function so the user can pick a site based on the client, but then he wouldn’t be able to add a new site on the spot

[LeventK]

@Rafael_ANEIC-PY
In your Transactions table, you can re-configure the type of [ClientSite] column from Text to Ref and choose Sites table as the referenced table. When completed, you will notice a Virtual Column under Sites table named as [Related Transactionss]

[Rafael_ANEIC-PY]

Hello @LeventK, thanks for replying, i have just done that, but it shows me the whole list of sites, and not only the ones that belong to the client that is related to the current transaction.

That’s what makes me doubt, is it possible to get the filtered list AND be able to add new rows?

[LeventK · Accepted solution]

Yes it’s possible @Rafael_ANEIC-PY
You need to use a Valid_if expression for the [ClientSite] column in Transactions table.

SELECT(
    Sites[Site_ID],
    [Client_ID] = [_THISROW].[Client_ID]
) 

[Rafael_ANEIC-PY]

Hello again @LeventK, i didn’t know you could use a Valid_if like that, it worked like a charm, thank you !

[LeventK]

You’re welcome.