Many To many with a dynamic form

[Stephen_Monagha]

Hello,

I have a table that is a check point table. It has check points that can be edited by an administrator. So for example it can have 20 check points, but these check points descriptions and names can change, as can the number of check points. These could change on a daily basis. There will never be more than say 30.

Then I have another table of e.g. cars. And when a new car is added, the user has to answer all the check point questions and say if they are ok on the car. If they are not ok they can add a comment or even a picture.

So far I have a car table, and a checkpoints table and a bridge table linking them. This bridge table has the comment column and picture column which allows the user to add specific comments and pictures for each checkpoint on each car.

When I set all this up using default behaviour for refs and add a car, the user then has to manually add a New entry for each checkpoint. Since the checkpoints are already defined in a table I don’t want the user to add them manually. I want the user to just have to answer each checkpoint question and add a comment.i.e. I want to present a form of all the checkpoints and when the user saves this, the relevant tables will be updated.

How would I go about this doing in appsheet?

Thanks in advance for any help.
Steve

[1minManager]

I’m sure if i’ve understood this correctly. But I’ve done a few similar sections…

In the car table, could you not add all the checkpoints as columns
[Checkpoint 1]
[Checkpoint 2]
[Checkpoint 3]
[Checkpoint 4]
And use Show_IFs to control what the user sees?

Also note you can use a formula to control the column description too.

[Stephen_Monagha]

Thanks for your reply Simon.

How would the underlying tables look if I did it this way?

I would have a checkpoints table, with for example the current 30 check points descriptions in one row. Then I would have the car table. Where would I keep track of the comments or pictures entered for each checkpoint?

Would the best be to keep that information also in the checkpoint table? So every time a new car is added, then a new entry is created in the checkpoint table that is linked to the car table entry?

[1minManager · Accepted solution]

Hi Stephen, sorry I missed your response

So the thing I don’t know is why the list of questions would change, I’ll assume for this example the questions change depending on the cars colour.

So the Checkpoints table will simply by to cross reference which questions to ask based on the colour and will look like this
[Colour] | [Question1] | [Question2] | [Question3]…
Red | How many doors | How many seats | How many wheels
Colour can be your Key if they are all unique

In the Cars table you’ll need an identical set of columns
For [Colour] you’ll need a Valid_If of Checkpoints[Colour]
For [Question1] you’ll need to have a Display Name formula of
Any(Select(Checkpoints[Question1],[Colour]=[_ThisRow].[Colour]))
Then a similar one for each Question
For neatness I’d also make Colour required and make the Show_If formula for the questions to be ISNOTBLANK([Colour]). Since the questions would show but have no title until a colour was chosen.

Hope this helps

Simon@1minManager.com

[Stephen_Monagha]

Hi Simon,

Thanks for your response. That makes sense to me. Will give it a try.

Stephen