Parent - child with "multiple Parents"

I have 2 tables, lets call them Company and employees, i can add notes to both views that are linked to the Company ID or Employees ID, each of these tables have a column called Status, when i create a new note i would like it to fill that status in to the note table but as i have 2 parent i cant just use the normal ref, is there a way to say, take Status from PARENT and put it here?

0 11 1,695
11 REPLIES 11

GreenFlux
Participant V

Thereโ€™s a section about Many-to-Many relationships towards the bottom of this support document that should help.
https://help.appsheet.com/en/articles/895267-data-the-essentials

Im sorry, i dont see where i can see the solution to my problem, i am being explained about how databases work but not how i, in appsheet, draw from a parent a field to a specific field in the child

GreenFlux
Participant V

Iโ€™m not exactly sure what youโ€™re trying to do.

  1. Do you want to DISPLAY the Parent-Status in the Child-Record, OR
  2. Create a separate Status in the Child-Record with the same initial value as the parentโ€™s current value?

For #1, create a Virtual Column in the Child table, with formula:
[ParentRef].[Status]

For #2, use the same formula in the Initial_Value setting of a regular sheet column.

I want to copy the parent.status to the child.status, this would not be a problem if parent always was the same, but because i only wanted one table containing notes, i have ref to notes from 2 tables
So it is something like #2 but i have 2 parentsโ€ฆ

Do you have two different Ref columns in the same table? Can you post screenshots of both tableโ€™s column settings?

You donโ€™t have a Ref-type column in your Notes table. I think youโ€™re going about creating relationships the wrong way.

Did you add the Related Notes-Ref_Row manually? The normal approach is to add a sheet column in the child table of type=Ref, and store a key for a parent record.

Adding a Ref-column in the child table automatically creates the Ref_Rows for you in the parent. It looks like youโ€™re trying to build it manually, but there is no Ref-column in the child (Notes) table.

Also, you may not need two parent tables, but rather, two slices of the same table. Perhaps it would help if you explained the use-case and data involved. Iโ€™m still not sure what your end goal is.

the two parent tables contain completely different data.

it is a bit hard for me to explain because it is very specific to my work but lets say:

table 1: lets call it โ€œCarโ€
Car_ID
Model
Color
Status
Notes

Table 2: Costumers
Costumer_ID
Name
Phone number
Email
Status
Notes

Table 3: Notes
Note_ID
Source_ID
Note
Picture
File
Status

So when i create a note on a car, it will store in the Notes table with the Car_ID as source_ID and when i create a note on a costumer, it will store in the Notes table with the costumer_ID as source_ID
Now i would like it to automatically fill in the Status field so i can see what the status was when i made this noteโ€ฆ

Does that give any sense?

And thank you alot for your patience and your help

Just add a second Ref-type column to the child table and make each Ref point to a different table.

Change Notes[Source ID] to [Car_ID], and add a column for [Customer_ID].

When creating the child from the new button shown on the parentโ€™s Related-Records inline view, it will autofill the parent Ref for you.

You will probably want to use the Required_If to make one of the two Refโ€™s required, and lock them from editing when not blank.

[EDIT] For this use case, I think it would be best to hide the Ref not being used. So each new note will always be created with one of the two Refโ€™s filled in, and the other hidden so it doesnโ€™t confuse the user.

Use the Show_If formula to reference the other Ref using ISBLANK([Other_ID]).

Thank you for your input, i will try to make it work tomorrow, it is late (11pm) and i am trying to remove action buttons from specific views ๐Ÿ˜•
i am only a newbee to thisโ€ฆ

MultiTech
Participant V

For those trying to create a many-to-many relationship: just dropping in a link to a summary post I made, which contains a sample app, about how to accomplish this.

Top Labels in this Space