Linking new row in one table to column value in another table

[Wallace_Service]

Hi everyone! Wasn't sure how to best write a subject but here goes.

I have a form view that adds a new row to a Projects table.

The "Please choose the customer name." column is a Ref to a Customers table.

The "Please choose the customer site." column is a Ref to a Customer Sites table.

Each row in the Customer Sites table would be linked back to row in the Customer table via key column.

If I don't have the customer site option I need in the "Please choose the customer site." column, then I can add a new Customer Sites table row with the New button above.

When I do this, Appsheet doesn't know which Customer row to relate to, so it's doesn't create the key connection.

What I need to do in this case is pull the key from the referenced customers  table from the "Please choose the customer name." column. 

How would I pull this value from the form view to create the connection?

Thank you in advance!

[Suvrutt_Gurjar]

Could you try below?

In the Customer Sites table , in the reference column that references Customer table, could you try an initial value expression of 

INDEX([Related Projects][Customer Name] ,1)

Where [Related Projects] is the reverse reference column in the Customers table and [Customer Name] is the reference column in the Projects table that references Customer table

[Wallace_Service]

Hi @Suvrutt_Gurjar , thank you! I tried it and it doesn't seem to find a value either. Initially I had [Customer ID].[ID] as the initial value. Customer ID being the Customers Sites table column that references the Customers table. ID being the key of the customers table. Which, of course, works fine when adding from an inline customers sites table in a Customers details view. Let me know if I can provide better info. Thanks!

[Suvrutt_Gurjar]

My retesting showed it works for me.

You may want to share in which way it does not work or could you share the screenshots of the expression from the column and relevant column screenshots of those three tables?

I mean, please share the table column details in the following format

[Wallace_Service]

Hi @Suvrutt_Gurjar, finally got a chance to get back to this today! Here's the screenshots! Thank you in advance.

[Suvrutt_Gurjar]

Thank you. All seems to be in order. Hope you are ensuring that after saving the new customer-site form,  you are also saving the projects form. This is so because customer site record will get saved only after you have saved the projects record.

Hope you are also ensuring that the Customer Name is entered in the projects form before selecting the new button to add a new site.

In the example below,  when a new customer site form is opened from within the projects form, please note the Customer name is carried through in the customer sites form. After saving the customer site form , please note that the projects form that initiated the customer sites form is also saved. My test setup is exactly identical to yours.

[Wallace_Service]

Hi @Suvrutt_Gurjar thank you for the guidance so far. Coming back to this again and have found that my issue is that the Customer does not automatically populate my equivalent of your Customer_ID column in the Customer Sites form. If it choose the correct customer in that column manually it will all link properly.

[Suvrutt_Gurjar]

Thank you for revert. I have no more suggestions at this stage because in exact similar setup it works for me.

Maybe someone else will have a suggestion/solution.