How can I create a items in a table, which re...

(ControlProcess Pav) #1

How can I create a items in a table, which refer to another table?

And then later select those same created items for another table?

Let me be more specific.

I want to create Objetives for the Year. So I create 2018 Objectives item, and inside it, I add several Action Plans.

Objectives is one table. Action Plans is another table.

One-to-Many relationship.

So far so good.

But then I have another table, the Risks table. I create risks in this table.

Now, I want to select some of those previously created action plans. Each risk may have several action plans. Each objective may have several action plans.

Each action plan may be related to several risks and each risk to several action plans.

I did create a ref column on the Action Plans table, refering to the Risks table.

However, at the risks table, while I do have a view of the Action Plans inline table, I can only create NEW action plans instead of being able to select some already made ones.

Any idea how should I design these DBs or the App to be able to do what I want?

(Tony Fader) #2

@ControlProcess_Pav Sounds like your Risk-Plans relationship is many to many. In that case, you need to introduce an intermediate table that has a reference to both the Risk table and the Plan table.

(ControlProcess Pav) #3

The intermediate table only has two columns, both Foreign Keys, right? But won´t AppSheet demand that one of those two columns be a key? Or do I create a third column which will be a Key (where it values do not repeat)?

(Tony Fader) #4

@ControlProcess_Pav You could make a separate key column if you want to allow duplicate pairs. Otherwise, make a key that’s the combination of both keys, e.g. [Ref1] & “,” & [Ref2]