Two Reference tables are part of each other

[Joel_Tjon-A-Tsi]

Hi Appsheet community,

Here goes,…

I have a table called: Projects, and another table called Assets. In the table Projects i can create a new project and ref to assets that are a part of this project. In the Table Assets a store all my rooms which are all unique. In a detail view of a asset i can see all related projects that this asset has been a part of.

This was created using a table in the middle called: Project_Assets. This table holds a project ID and a Asset ID.

So far so good,…

Problem to solve:
Situation now: When i create a new project, and want to ref to 10 related assets, i have to add all assets one by one.

Wishfull situation:
When a new project is created i want to multi select different assets at once.
Idea on how to achieve that: in a table view of the table assets, with the function “Quick edit” on. I want to multi select different assets and execute an action: an action where i wright the asset ID on the related middle table: Project_Assets. problem here is that the project ID is not created at the time this action is executed…

Any ideas???

gr Joel

[WillowMobileSys]

In the Tips and Tricks section, @Steve informed us of a solution where you could add an arbitrary number of rows to another table. This was based on a user selected numeric value.

I extended that same functionality to work with an EnumList where multiple values were selected from a dropdown.

The idea, in your case, would be that when creating a Project, you have a dropdown list of Assets in EnumList form. When the project is Saved there is an attached custom Action that will kick-off a series of Actions to create rows in your Project_Assets table, one for each Asset in the EnumList.

Below is the link to my post on @Steve 's Tip. I would review Steve’s functionality at the top of that post to understand it. Then hopefully my section (linked below) will make sense.

Of course, we are here to help when you need it!

Add any number of rows from any row within a table Tips & Tricks 💡

Not sure if this will help. @Steve 's approach outlined here was the succinct answer to a couple similar problems I had been working on. Pick from a list of Technicians to assign to a work order and then place them into an Assigned Technicians table where each Tech can later record their personal job stat info (e.g. Start and Stop times on the job). Pick from a list of Products or Materials or Labor Charges to apply to a Work Order - populate them into their respective tables and then al…

[Joel_Tjon-A-Tsi]

Hi John,

To resume your trick and that from @Steve it shoul look something like this:

I adjusted @Steve 's approach to use picked list items from an EnumList rather than a count column. Then modified the following actions defined in Steve’s outline.

• In the action to add the single row, I assigned the Technician column to the FIRST item from the picked list using the INDEX() function like this:
  INDEX([Picked Technicians], 1)

Do this in this step from @steve plan.

• In the action that normally decrements count, I instead REMOVED the first item from the picked list like this:
  [Picked Technicians] - LIST(INDEX([Picked Technicians], 1))

Do this in this step from @steve plan,

The result is that I have a set of Assigned Technicians each with a different Technician assigned.

Does this make sense?

Thnx in advance!

gr Joel

[WillowMobileSys]

Yes. I think you’ve got it. You may need to make some additional tweaks for your use case.

In the Add New Row action that adds a row to your Projects_Assets tables, you would add to the “Set these columns” section

• a column for your Project ID = [_THISROW].[Project ID] (replace with actual name of your columns)
• a column for Asset ID = INDEX([Picked Assetss], 1)
• any other columns you are able to assign

[Lynn]

HI @Joel_Tjon-A-Tsien
Did you get this to work for you?

[Joel_Tjon-A-Tsi]

Hi Lynn,
No not yet, i am stroggeling as we speak,

The solution sugjested by @Steve and @WillowMobileSystems works if you work in one table. The difference is that i start in table Prjojects, but i want to execute the actions on table Projects_Assets.

The steps 4 till step 9 need to be executed on table Project_Assets. But i need to trigger them by creating a project in Table Projects.

any idea’s??

gr Joel,

[Lynn]

Hi @Joel_Tjon-A-Tsien
I got myself bogged down also trying two tables. Will let you know if I find anything that works .

[WillowMobileSys · Accepted solution]

Joel_Tjon-A-Tsien:

No not yet, i am stroggeling as we speak,

Joel_Tjon-A-Tsien:

The solution sugjested by @Steve and @WillowMobileSystems works if you work in one table.

In the action that saves rows to the same table, you would simply change that to the table you wish to save to - e.g. Project_Assets.

@Lynn
Let me know where you are struggling and I can help you both through it. I have implemented this so I know it can be done - unless of course if your use case is different than I am understanding.

[WillowMobileSys]

So here is the quick reference for being able to add rows to a secondary table when Saving a row on your main/initial table.

This assumes you have created some multi-selection pick list in an EnumList column that is part of your main table.

1) Save Action - attached to the “Form Saved” Behavior on the Form.

2) Criteria that determines if action should execute - i.e. List has values

3) A Grouped set of actions that controls the flow for each pick list item

4) Add a new row to the target table using values from the main table row

5) Remove from picked list the item just added to the target table

6) The Grouped Action will then call back to the original Action to do again for the newly updated pick list.

7) Continue 1) - 6) until pick list is empty.

[Joel_Tjon-A-Tsi]

@WillowMobileSystems almost there!!

I can’t figure out what you mean by: “Different Action”,… in your picture it is the same as the action that you are creating,…

What should be the right action here?

gr Joel,

[WillowMobileSys]

Sorry, I probably should have just left that off. I was trying to avoid more arrows.

My concern was that, with the talk about recursion in this methodology, some might mistake that the “Action name” and the “Referenced Action” were the same action. In fact they are different, one is with an “S” at the end of the name while the other isn’t.

[Lynn]

@WillowMobileSystems
Thanks for the update. I will try again tonight.

[Joel_Tjon-A-Tsi]

@WillowMobileSystems

So it’s working for 90%!

So far so good. The thing now is that it stops after creating 2 rows in the reference table. For example:

I select 5 assets in the ref. Enumlist column, 1110, 1120, 1210, 1220, 1230 and 1310. The action sequence initiate that asset 1110 is created in the ref. table Project_Assets and erased from the list (ref. Enumlist column). asset 1120 is created in the ref table Project_Assets, but not erased from the ref. Enumlist column.

I think that there is some thing wrong in the code: “[Room] - LIST(INDEX([Room], 1))”

Any idea’s??

gr Joel

[WillowMobileSys]

I think you should first check for any reported errors. To do this go to any one of your Workflow rules, expand it and at the top you’ll see a button named “Log”. Tap it to open a Log report. Then search the Log for any RED text items. Those are errors. Now see if one is reported against one of your Actions. If so, click on the Binoculars icon to drill into it and see what the error message is.

Otherwise, I am seeing in your image that the list is named Room but in the child table there is a column named Room as well. This won’t cause a problem UNLESS you have referenced the wrong table somewhere. Since there were two rows created this is likely not the problem.

Lastly, double check your STOP criteria. In the initial Action that kicks this off, there should be, in the Behavior section of the Action and in the "Only if this condition is true section, an expression like this: Count([Room]) > 0

[Joel_Tjon-A-Tsi]

@WillowMobileSystems

The log shos no errors, and the code: “COUNT([Room])>0” is in the last action: “Add more rows”

The code “[Room] - LIST(INDEX([Room], 1))” works in this case just one time, but stops after the second time,…

strange,…

What could it be?

gr Joel,

[Joel_Tjon-A-Tsi]

Fixed!!!

(the last action was referring to the first action instead of the action sequence,…

Thnx @WillowMobileSystems !!!

on to the next challenge!

gr Joel

[WillowMobileSys]

Awesome! Glad you got it working.