Creating records in related table

[lauratilton]

I'm super new to AppSheet so I really appreciate being able to ask questions here. (I give back in the Looker Studio forum, if anyone needs help there!) Simplifying my use case here and hopefully I'm using the right terms and things so my question is clear.

I have a Products table, and also an Expenses table. I want to have one (and only one) record in the Expenses table for each Product. But the Expenses table only has records for a few Products so far. I have set Product as a Ref field in the Expenses table and it is also the Primary Key in both tables.

I created an action on the Product view that links to the filtered Expense view for the given Product, and in that view Quick Edit is turned on so I can edit the necessary Expense fields. It's something like this:
LINKTOFILTEREDVIEW("Expenses_Detail",[_thisrow].[Product]=[Product])

The action works great to take me to the editable Expense view when the Product already has an Expense record, but if it doesn't, it just takes me to a blank screen. How can I get the functionality to see/edit the Expense record if it exists, or create the Expense record for that Product if it doesn't?

I don't think I want to link to the Form view because there are calculated fields in the Expense record that I need to see, and it looks like Forms only show editable fields? So Quick edit in the Detail view seemed to make sense.

Am I at least on the right track? Questions or ideas welcome.

Laura

[Suvrutt_Gurjar]

---

@lauratilton wrote:

I have set Product as a Ref field in the Expenses table and it is also the Primary Key in both tables.

---

If the Expenses table is child table of the Products table, you may want to have a different primary key for the Expenses table and having the reference field to refer the Products table.

The child table records are typically shown in the detail view of the parent. You can use the "Add" button to add an Expenses record.

Also if there is just one to one relationship between Product and Expenses tables, you may want to evaluate adding the Expenses fields in the Product table record only and doing away with the Expenses table, unless there are some specific requirements to have one to one table relationship and maintaining the Expenses table as a different table.

[lauratilton]

Thanks for the response. The issue with using the "Add" button is that I only want ONE record per product. I did find a thread on here about how to restrict the number of records, but it didn't seem to work for me because of the Ref field connection. But, you are right that I could just add the expenses to the Product table and that would simplify things greatly. 

I think I'm really just trying to get a handle on functionality where I would have unique items in one table, and want to click a button and add an empty related record for each item to another table that can then be tilled in. So, in the scenario above, maybe I just want one expense record per year per product. So I want to be able to mass-add empty expense records for the year 2025 to the entire product list all at once. Does that make any more sense? I know it's vague.

[Suvrutt_Gurjar]

You are welcome.

---

@lauratilton wrote:

he issue with using the "Add" button is that I only want ONE record per product. I did find a thread on here about how to restrict the number of records, but it didn't seem to work for me because of the Ref field connection.

---

You can certainly hide the add button with an additional read only slice based view on the child table. Please take a look at the following search results where a number of similar solutions are discussed,

Search - Google Cloud Community

---

@lauratilton wrote:

I think I'm really just trying to get a handle on functionality where I would have unique items in one table, and want to click a button and add an empty related record for each item to another table that can then be tilled in.

---

You could add several records to a table using webhooks.

Invoke the AppSheet API from a webhook - AppSheet Help

Invoke the API - AppSheet Help

Use Start expressions in templates - AppSheet Help