How to move data from one table to the next to a finished Exact Form document?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I've been working with QB for 4 months now, and I'm trying to work through a possible obstacle. I have two separate tables, Incidents and Investigations. I have built additional suppoting tables to help auto-populate lookups and drive better "Rubik's Cube" analytics of the data (i.e. what service had the most incidents, what cause codes were associated with the incidents and investigations). Here's my process flow:

1) An incident record is opened, given a unique ID, and the data in that form can be associated to a PDF After Action Report that would use Exact Form template.

2) If an incident record requires further investigation, system operators and investigators should click the Add Investigation button, and then populate the Investigation form with additional information.

3) The finalized Investigation record would be composed of data originally pulled from the Incident "form" that could be updated or modified based on the results of the investigation as captured in the Investigation record. The Investigation would then be transferred to an Exact Form template in PDF format for contract purposes.

I've been trying to figure out how to migrate the data from the Incident table into the Investigation table with the least adminsitrative effort. Is there a feature I'm missing in QB that I can leverage for this task? Would I be better off to create a merged table (say, Final Report) that would merge the data from the two tables into the third table?

Photo of Jeff


  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Lauren


  • 0 Points
The easiest way to do this would be to modify the Add Investigation button to bring that information over when the record is initially created. Here's an example of how you can do that:

Original Button Code:

URLRoot() & "db/" & [_DBID_INVESTIGATIONS] & "?a=API_GenAddRecordForm&_fid_8=" & URLEncode ([Record ID#])& "&z=" & Rurl()

In this code, the section "_fid_8=" refers to the Field ID for the Related Incident field on the Investigations table. In plain English, this button says to add a new record ( ?a=API_GenAddRecordForm ) to the Investigations table ( [_DBID_INVESTIGATIONS] ) using the standard Add form ( ?a=API_GenAddRecordForm ), and push the current Incident record's Record ID# ( [Record ID#] ) to the Related Incident field on the new record ( &_fid_8=" & URLEncode ([Record ID#]) )

To change it to bring over additional information, you would make your changes just after the "&" after the Record ID#, like this:

URLRoot() & "db/" & [_DBID_INVESTIGATIONS] & "?a=API_GenAddRecordForm&_fid_8=" & URLEncode ([Record ID#])&

"&_FID_10" & URLEncode([Date]) &

"&_FID_11" & URLEncode([Text]) &

 "&z=" & Rurl()

In this new code, FID 10 on the Investigations table is receiving information from the [Date] field of the Incidents table, while FID 11 is getting info from the [Text] field.