Folder Pathing Utilizing Child Record Values as Naming variables

[zchichak]

Hi there, 

I am having some trouble with file storage and file "foldering" within an application. I'll do my best to be concise while providing all needed information. The primary problem is found when a folder path is being generated from the child File table, as I want it to grab field inputs from the other 3 input tables but these values fail to appear in the folder structure when it creates. Based on the order of events and a few other hints (see below numbers 5-7) I believe this is due to the data of the other tables not yet being synced, so it is not able to generate the proper path. When a file is stored the foldering system is only capturing the string values on the CONCATENATED folder path. Other information:

1. I have a parent table which is the primary intake table (Intake Table)
2. There are 3 child tables that have a many-to-one relationship with the parent
3. 2 of the 3 children tables have a many-to-many relationship (Customer_Table, and Product_table) 
4. The 3rd child table, and the table giving me issues is not related to the other children tables.
5. There are 2 bots setup to trigger on submission (both with the outcome to send an email), each having a conditional difference depending on some of the users selections on whether to execute or not.
6. One of the tasks generates a "snapshot file" which then triggers a folder path creation. 
7. The folder path creation utilizes inputs from all 4 tables, and when created through this generation, and works as intended and the folder path is accurate.
8. I have a condition applied to the bots so that they only trigger once all the child tables are synced using a grouped action + status field update. (This is why the folder path is created properly)
9. The files that were submitted in the child table are foldered incorrectly, the path only recognizing the file path variables in the concatenated that are text strings, and ignoring the values that are supposed to be pulled from other tables (or they are being pulled before the data is synced) 

How might I delay the file storage from the File table to happen after the other fields sync takes place? Is there a delayed file sync that I am missing? 

[Marc_Dillon]

This is hard to follow, please attach screenshots.

You're generating a file via a Bot? And it is not going to the GDrive folder that you're wanting it to go to? Is that correct?

[zchichak]

The bot that is creating the file and the location is working as intended.

The part is not working as intended are the files that are attached during the form fill-out as a child record.

I hope this helps, as I am not sure what screenshots to provide you with to assist.

[Marc_Dillon]

"The part is not working as intended are the files that are attached during the form fill-out as a child record."

What is your setting for here:

[zchichak]

This: 

Which is this:

And each of those are individual Select() expressions pulling children from other tables.

[Marc_Dillon]

So the folder path is being generated in the parent record, using data from the 1st and 2nd child tables? Is this a Virtual or Real column?

Then you're pulling that path value via de-reference into the folder path of a file column in the 3rd child table?

In what order are you adding all of these records? Are you adding child records from within the Form of an un-saved parent record (not recommended)?

After you provide the answers, I may be able to give a suggestion. However, my personal opinion is that this is a terrible way to setup your folder organization. Why do you have it setup like this, and is there any flexibility on this setup?

[zchichak]

The column is virtual.

Your assumption of the un-saved parent record is correct. It is unfortunate but based on the flow of the data this is how it needs to be configured, unless there is another way that would allow for the same flow of the user form?

If you are able to provide a better way which you would recommend to set this up, I would be open to it. The problem is that the relationship between the tables needs to remain the way that they are. The output of the folder structuring needs to have this specific naming convention.

If there is a better way to create the structure using the same inputs I am open to anything! I cannot change too much in regards to naming convention, due to other stakeholder requirements. 

[Marc_Dillon]

You should force the user to save both the parent record, and all child records from the first and second child tables, before adding any records to the 3rd child table.

It's still a bad setup to me, it's just begging to cause issues in the future. What happens when you have to change one of the values in a child record that the folder path is relying on?

[zchichak]

I can see reasoning why this may be the best option in regards to ensuring the solution works, but by doing so I lose the inline functionality of adding a row to another table, as part of the parent submission.

I can see where there would be concern for changes to a child row in some instances, but based on the nature of these workflows that is a minimal risk. That being said, even if a child record were updated, the parent folder of this system is saved using a Unique ID that will not change in an instance. However the files within that parent ID folder are subject to change but it is extremely unlikely as they too are identified using a unique id per transaction.

All this being said, I am considering to use a bot to watch for a modifiedtime value in the child tables that will return the needed values to the parent table once entered. Not sure if it will work, but I need to find something based on the timeline.  

[Marc_Dillon]

"a bot to watch for a modifiedtime value in the child tables that will return the needed values to the parent table once entered."

Not 100% sure what you mean here. But no Bot can be triggered without first saving a data change, so I highly doubt you will find a solution there.

------

I think you're greatly exaggerating the difference in "flow" between adding all children from the parent's form, versus adding them from a saved parent.

Here is something you can try, to maintain a good flow while forcing the parent to be saved before adding to the 3rd child table. Create a new Slice of the parent, and a Detail View on this Slice. Only add 2 columns to this new Detail View, a Show type column with some simple instruction, and the REF_ROWS VC for the 3rd child table. Send the user to this new View immediately after they save the parent record.

Forcing the parent to be saved first has another major benefit too, preventing loss of data due to user mistake. As an experienced appsheet app builder, it only took a small handful of times of users and clients complaining about losing all of their work because they accidentally hit back too many times and cancelled out of the entire form stack (parent and multiple children), before making the change that I'm suggesting as standard building procedure. I know you don't think it is optimal, but I am just trying to save you lots of time and frustration by giving advice based on that experience.

------

If you really don't want to make that change, another option I can think of is to build a Google App Script that will run when these records are saved. The script would re-calculate the file path from the records, create the new folder, find and move all of the uploaded files from the initial folder to the target folder, then update the relative file paths in the records.

[zchichak]

I was going to have the bot act as an event listener and look for a change to the modified time stamp, but after your consideration I understand this wont work as the form data entry is not auto saved. Even if I switched the child record to save the form on completion, I believe that sync would not be forced until the parent record is saved.

Thank you for taking the time to explain that to me, and I have deployed multiple applications at my organization that have a hefty form "stack". I will give your solution a try tonight, and if it goes well I will incorporate it into future builds.

Data loss is something I have considered based on the system functions around the webpage based back button and how it interacts with nested forms.

As time is getting more pressed the App Script option had crossed my mind, but I would rather have the functionality executed on AppSheet's side as the application is going to be passed onto a business unit that would not have the knowledge to maintain the App Script.

I appreciate your assistance here, and Ill mark the solution once I give it a try.

[zchichak]

Alright, after trying a few methods to have the UX flow as intended, I went back to looking into a possible solution for child tables that are part of the parent.

What I ended up doing as a solution was creating an actual column in the parent table that performed the select statements from the children table. I found since the field utilizes the formula input, the data from the child tables are being calculated while the form is being filled. (Different than virtual columns?). This means when the Folder pathing feature looks for the proper string to folder as intended, the data is already there.

After a number of tests, it seems that the child records and folder pathing is working as intended.