QB Action that creates a related record in another table when new record saved.

Question

In our CRM we have the ability to "attach" documents to other types of records such as "client activities". In order to have the ability to related several records to one document, we use the following data storage topology:

Table: "Documents" - this is where we store actual document file, description, etc.
Table: "Document Selections" - this is where we store the relationship between a record in other tables to the document record in the "Documents" table.
Table: "Activites" - this is the table we use in our CRM to log interactions with clients. Users also have the ability to attach/relate an existing document record in the "Documents" table to the Activity record OR have the option to add a NEW Document record that would relate to the Activity record.

My issue is that the current process for creating a new Document requires the user to select the "Related Document" dropdown and scroll all the way to the bottom of the dropdown list to select the "+ Add New Document" option. From here, they open yet another window/screen to upload the document. By now, they have been presented with three different forms.

My attempt at a solution was to add fields to the "Document Selections" form that would allow the user to upload a file and two other fields that mimic that of the "Documents" table. I've been trying to create an action that would copy these fields to the "Documents" table, which works, but does not then write the "Documents" `Record ID #` to the `Related Document` field back to the original "Document Selections" record that triggered the action originally. I tried creating a similar action in the "Documents" table that looked for a new "Documents" record with a static numeric field called "Related Document Selection (static)" that contains the `Record ID #` of the original "Document Selections" record that was created, however, this ends up creating a NEW "Document Selections" record, rather than updating the current record.

Any help on this would be much appreciated, thank you!!

------------------------------
Matt Makris
------------------------------

donlarson · Answer

Matt,Is this your architecture?&nbsp; I made up additional table names as parents of the Document Selections.

If this ERD is wrong, then please correct me.Without knowing more about your business case, I would change design so that the Documents table is related directly to the other Business Objects: Tasks, Opportunities ......
Now in each Parent Table you can modify the Add Document Button to take your Users to a specific form to add the Document for what ever your business rules require.If the Document record needs to be related to an additional Parent in the future, you can have another QB Form that drives that process.This should solve the issue in your question but there will be more things popping up for Document management.&nbsp;

------------------------------Don LarsonPaasporterWestlake OH------------------------------

mattmakris · Answer

Hi @Don Larson​,You nailed the data architecture (1st diagram). The reason we designed it this way is so you can 'attach' multiple documents to another record, such as an activity, while having the ability to 'attach' existing documents that records in other tables are linked to, so that we don't have to re-upload duplicate documents. For instance, if an Opportunity is linked to a proposal document and a contract document, I'd also like to link a task to those same documents. This way we aren't loading our database up with duplicate files and we're able to link multiple documents to single records..------------------------------Matt Makris------------------------------

donlarson · Answer

Matt,I got lucky on the diagram so now let me make sure I understand the work flow problem with this architecture.Is the User issue adding a New Document and Document Selection&nbsp; OR is the problem adding a Document to an existing Document Selection that is already related to the Activity?------------------------------Don LarsonPaasporterWestlake OH------------------------------

mattmakris · Answer

Don,The problem is that there are too many steps to add a new document that is related to the Activity record. Currently the user has to load the new Document Selection form, then from the Related Document selector they select "+ Add new document". From there a new window opens and they have to fill out the "Add New Document" form ('Documents' table), then go back to the 'Add Document Selection' form, then back to the 'Activity' record they were adding or editing.&nbsp;&nbsp;I am trying to figure out how allow them to either link to an existing document or add a new document. If a new document is requred, then I'd like them to only have to view/complete one form, rather than viewing several screens to do what would otherwise be a simple task.Matt------------------------------Matt Makris------------------------------

donlarson · Answer

Matt,Allow me to rename the middle table.Now we can think of that table as the bundle of documents that need to be related to a variety of different parent tables.The User has three different workflows that we have to support even though they just think of it as adding a document.&nbsp;&nbsp;1) Create a new Document Package2) Relate an existing Document Package to the Activity3) Add Documents to a Document Package that is Related to the Activity3) is the easiest.&nbsp; &nbsp;On the Activity form you can put an Embedded report that shows all the Document Packages that are related to the Activity.&nbsp; &nbsp;Make sure there is an Add Document button and the Package gets bigger by one document each time.

2) Getting this right requires more information about the other tables in the application and how you tied them together.&nbsp; If you have a Package related to an Opportunity that you now want to also relate to the Activity, you can have a report of all Packages not currently related to an Activity.&nbsp; A quick Grid Edit will then relate that package to the Activity and all the Documents in that package will be tied to the Activity.&nbsp; &nbsp;This will get tedious quickly as the number of Packages in your application grows.Any chance there is a hierarchy of the other tables to take advantage of?&nbsp; &nbsp; Something like this:Now the Users can search for Packages that all share the same Related Client going up the relationships.On you Activity Form is going to be a different button for the User to Search for the package to relate.&nbsp; It will be a link to the correct report with the filters needed to make the Users life easy.Use case one is the most complex because the User has to do everything.&nbsp; The complexity of your Document Package form and the number of required fields before you can add Documents to the Package really impact the workflow.Lets assume you have no required fields in the Document Package.Name the button on the Activities Form&nbsp; &nbsp;Create Document Package.&nbsp; This will help the User understand that they are not in situation 3)Go to the Settings on the Document Package Table and click the check box for Save Parent Record

What this will do is take one click out of the process for the User.&nbsp; When the Users on the Document Package form the first time and they click Add Document, the Document Package will auto save.------------------------------Don LarsonPaasporterWestlake OH------------------------------

Forum Discussion

QB Action that creates a related record in another table when new record saved.

Related Content

Show related records

Moving Fields from One Table to Another

Relating Multiple Records from another table

AI Smart Builder for Creating Apps and Streamlining Pipelines

Filter out some records on related table