Hi everyone.
I have a table called Suppliers with the following schema :
sid(key) Name Address Webite
The table has a weak entity table (is part of) thats called “Supplier additional details” with the following schema :
sid, Purchase method, Apple DEP enabled, Tags
Now that Ive configured it that way, I have a new virtual column in my Suppliers table that references those rows. ive marked it as required so that when users make a new supplier on the app, then they must create that row on the child table as well.
Now, if I want to use the API to add multiple rows to the Supplier table, how do I do that?
I tried sending thsi json to the Supplier table:
{ "Action": "Add", "Rows" : [ { "Name": "test", "Related SupplierAdditionalDetails":[ { } ] } ] }
But I get:
“REST API invoke request failed: Error: Can’t add or update a row because a required value is missing. Provide the required value and try again.\n\n\tRow ID to correct: 4b5048fe\n\tMissing value in column: Related SupplierAdditionalDetails\n\tExpected data type: List”
Not sure what to do here as its impossible to get the current row ID (as its automatically generated) in order to make the record on the child label so it can dereference it.
What do I do?
You could change your required to a require_if statement? You may not realise that it is possible to require something based on a formula. In the case of when the user is adding a supplier via the app, then the formula for the require field might be CONTEXT(“ViewType”) = “Form”. This would force the user to add extra details if they were currently on a Form View.
You could get specific if you wanted and make the require_if something like CONTEXT(“View”) = “Supplier_Form”. That would make the column required if the user was on a specific view.
More contexts here CONTEXT() - AppSheet Help
1 Like
I’m unsure what field you are setting as required on the supplier table… the Related SupplierAdditionalDetails field?
Also, in your additional details table, add a key field: addDetailsID with the uniqueID() initial value. Your sid is a ref back to your suppliers table and shouldn’t be the key in the related table.
If the goal is to ensure there is always a related details record, I wouldn’t necessary make the virtual column required.
I’d just add a bot that runs on the add and update for the suppliers table.
The event condition can include something like:
COUNT([Related SupplierAdditionalDetails]) = 0
Then your step can use the api or just a data action to add a row to that table.
However, you indicated you want to add multiple rows.
Multiple rows for a single supplier or are you creating rows for your existing suppliers?
If multiple rows for a single supplier, what is the data that is being stored in those rows and what is its source? We can create a start expression in your API body to make that happen.
If you are adding rows for existing data, create the bot indicated about that checks the count of related data and, if none are found, creates one. Make it a scheduled bot, run it for each row in your Suppliers table.
1 Like
You would need a separate api task for the adds to each individual table. You can’t ever manually input a value into a virtual column.
Thank you all, I understand my error.
I would like to ask from your exprience, in appsheet, is it generally considered good practice to divide tables into nested tables like that? or am I better off putting all of the fields in the same table?
The reason im asking is because sometimes it just feels like Appsheet doesnt provide us with the features needed to play nice with these kinds of setups.
1 Like