Solved: Add multiple rows to another table dynamically

Pratyay_Rakshit · 12-04-2020 12:31 AM

There are ‘technicians’ on a job whose number can be dynamic. There’s a ‘job’ table which holds in a column all technicians on it using a list column type.

Technicians who have been assigned the job to can see their assigned jobs from a view from an appropriate slice.

In a job details table, set as children of the main job table, the technicians enter their visit details and the number of hours on a particular visit. There can be multiple visits.

What I want is to export the sum of hourly contributions for each technician in a job to some table, where the number of records added will be the number of technicians on the job.

How can I do this? Especially in such a case where the number of technicians are not fixed.

I know exporting the same data in a report can be done pretty easily, but I need to perform changes on the ‘total hourly contribution’ data, should I’m allowed to add it to the table in the format that I want in the first place.

WillowMobileSys

I would recommend building this hierarchy into the app such that when a Technician enters a “visit”, those hours are immediately and automatically added to a summary record.

To build the hierarchy, you would use AppSheets Parent/Child relationships. I am including the article below.

The hierarchy would be,

Job Table - contains all job details. There is no need to physically add list of technicians but you may want something like Total Man Hours.
Assigned Technicians table - Includes Job ID (key of Job row) and all summary details of technician work on the job such as Total Hours - a sum of Technician Visits. Setting Job ID for Parent/Child relationship automatically adds Assigned Technicians list to the Job Table.
Technician Visits table - Includes Assigned Technician ID (key of Assigned Technician row) and any other visit details including Hours. Setting Assigned Technician ID for Parent/Child relationship automatically adds Technician Visits list to the Assigned Technicians Table.

Technicians would access the Job, then their Assigned Technician row and then add a visit. With properly inserted formulas, the visit Hours would be automatically added to the Assigned Technician row and even to the Job details.

Assigned Technician would then have you summed details by technician. No need to force copy and calculate data.

Article below describes how to build Parent/Child relationships. Once you have a table structure like above, its just a matter of flipping a switch on the correct columns. You will want to scroll down in the article to the section named “Expression Ownership Between Table”.

View solution in original post

WillowMobileSys

I would recommend building this hierarchy into the app such that when a Technician enters a “visit”, those hours are immediately and automatically added to a summary record.

To build the hierarchy, you would use AppSheets Parent/Child relationships. I am including the article below.

The hierarchy would be,

Job Table - contains all job details. There is no need to physically add list of technicians but you may want something like Total Man Hours.
Assigned Technicians table - Includes Job ID (key of Job row) and all summary details of technician work on the job such as Total Hours - a sum of Technician Visits. Setting Job ID for Parent/Child relationship automatically adds Assigned Technicians list to the Job Table.
Technician Visits table - Includes Assigned Technician ID (key of Assigned Technician row) and any other visit details including Hours. Setting Assigned Technician ID for Parent/Child relationship automatically adds Technician Visits list to the Assigned Technicians Table.

Technicians would access the Job, then their Assigned Technician row and then add a visit. With properly inserted formulas, the visit Hours would be automatically added to the Assigned Technician row and even to the Job details.

Assigned Technician would then have you summed details by technician. No need to force copy and calculate data.

Article below describes how to build Parent/Child relationships. Once you have a table structure like above, its just a matter of flipping a switch on the correct columns. You will want to scroll down in the article to the section named “Expression Ownership Between Table”.

Pratyay_Rakshit

This is not the first time you have taken so much time to help me, and I’m more than grateful to you for this.

You’re awesome man ❤️

Pratyay_Rakshit

The thing is set and is working perfectly.

I have run into a problem whatsoever, and want to address it here rather than making a whole new thread.

I have a data change workflow running in the main job table, with an action to set the column values appropriately.

ifs(
and(isblank([Related Assigned Technicians]), isblank([Completion Date])), “Unassigned”,
and(isnotblank([Related Assigned Technicians]), isblank([Completion Date])), “Assigned”,
and(isnotblank([Related Assigned Technicians]), isnotblank([Completion Date])), “Complete”
)

The workflow is set to trigger on adds and updates.

Now the problem is when I add a new job and assign technicians (in child table), theoretically rendering isnotblank([Related Assigned Technicians]) to TRUE, the expected change to “Assigned” does not happen. It stays on “Unassigned”.

After saving the job record, if further I add more technicians to the job record, still then no change happens.

But when I change another field in the job table, the workflow triggers and the status does get changed to “Assigned”.

So I think the problem is with the related child records not being accounted for in the workflow. What’s the way around this?

N.B. A solution is there in the appsheet example gallery which uses an action that triggers when the parent form is saved, which updates a column in the parent, which triggers the workflow. But in this case here we might not add techs in the 1st lead entry at all. In that approach that would also false trigger the workflow as well