Adding child relationships by selecting from parent table

[Al_Amyot]

I have a Many-to-one relationship from table Apps to table FAF i.e. table Apps has a column FAF ID referencing table FAF. When editing table FAF, the related Apps are listed. However, if the Add(plus) button is clicked, I am prompted to create a new row in the Apps table. How can I require the user to select an existing row in Apps instead?

[LeventK]

You need a reverse ref for that. Set a REF column to child table with the isPartOf set to ON.

[Al_Amyot]

isPartOf=ON is used if the child exists only as part of the parent, eg. items in a sales order. In this case, the parent and child are created individually and separately and then later related . I need the user to be able to select the child from an existing table, but not able to create a new child when creating a new parent. AppSheet loads the form to create a new child when Add is clicked under the parent.

ANother example would be Stores and Sales Regions. Both are created as first class entities. A store is then assigned to one and only on Sales Region, but a Sales Region can have many Stores.

[LeventK]

Al_Amyot:

ANother example would be Stores and Sales Regions. Both are created as first class entities. A store is then assigned to one and only on Sales Region, but a Sales Region can have many Stores.

You are actually talking about the same thing. A reverse ref means whilst you are creating a Sales Region record (which is the parent), you can create multiple Store records (which are the childs) at the same time without the need for selecting a Sales Region. On the contrary, you can’t create a Sales Region records whilst creating a Store record.

In RDMS logic, a child is always a part of the parent. Without a reference is setup initially between 2 tables, there is no chance to link these table records together. Even-if you create the reference afterwards, then any existing record on the table will not have a reference at all.

Your statement: “isPartOf=ON is used if the child exists only as part of the parent” is wrong. isPartOf ref is set in parent table, not in the child table and it creates a belonging between the parent record and the child records. Which means; when you delete a parent records, all the related child records will be deleted as well. Provided you set a direct ref between the parent and the child (where parent table is ref’d from the child), then they don’t have a belonging between each other and thus records can be deleted unassociatedly.

If you don’t want your users to be able to create a new ref record and they always need to choose from a constant list of items, create a slice from the referenced table, set the slice to read only and then alter your REF_ROWS() expression to read from this sliced reference instead of the reference table.

Assuming that your ref table name is: MyRefTable
Your REF_ROWS() expression shall be: REF_ROWS("MyRefTable", "RefColumnName")
Create a READ_ONLY slice and named like: slice_MyRefTable
Your REF_ROWS() expression shall now be altered to: REF_ROWS("slice_MyRefTable", "RefColumnName")

Hope I’m clear enough.

[Al_Amyot]

sorry but I have to disagree with your statement " In RDMS logic, a child is always a part of the parent".

There are two scenarios where a 1-N relationship is used, which is why Appsheet has the isPartOf option on the parent (I never claimed it was set on the child).

Scenario 1
Take the common example of a sales Order and its LineItems . Order is the parent; ’ is a part of’ set on a Ref column referencing the LineItem table). This means that a LineItem cannot exist without its parent and also that LineItems are deleted when the Order is deleted (cascade delete in RDBMS terms).
In this scenario it makes sense to say Order 123 HAS LineItems (which of course refer to Products).

Scenario 2
Take the example of Teachers and Courses. A Teacher can teach many Courses; a Course only has one Teacher (at least in my school). The Teacher table has a Ref column to the Course table bit ‘is a part of’ is NOT checked.
A Teacher exists whether or not she is assigned any Courses. A Course exists even if no Teacher is assigned. Removing a Teacher does NOT delete assigned Courses; they continue to exist waiting for a new Teacher . In this scenario we would would never say 'Teacher Bill HAS a Math course; but we would say he teaches Math.