Merge Table - How to Automate merging of records?

  • 0
  • 2
  • Question
  • Updated 4 years ago
  • Answered

This is related to an AIA billing project -

Essentially a project has many sections, each of these sections has a total dollar amount assigned to it.  The number and type of sections is different for each project.  Monthly, we will bill the customer for a % of each of these sections - the % is variable for each section each month.  

So we create child records of "sections" to the parent "projects".  We also create child records of "billings" to the parent "projects".  The third child table is a merge table that contains one "section" and one "billing".  The net result of these relationships is that we know each month how much to bill the customer in total, how much in total is outstanding, and the disposition of each individual section i.e. how much has been billed, how much is left.

The functionality works fine, but is a real headache from the users perspective.  

What I am trying to accomplish is some way to dramatically simplify the process of merging a monthly billing record with all the existing sections.  In other words, get around the end user having to add each individual section to each billing / section merge record every time.  Every billing / section merge should contain every section that exists for the parent project.    

I haven't been able to accomplish this with the "copy master detail" records button function in app management. 

Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Is the situation that you expect to set up the Sections for each project manually, but then at some point you want to push a button and essentially duplicate those Section child records into another child table called Billings, on a one for one basis?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Can you explicitly state home many tables you have, their names and how they are related? Short like this:

A -< B
A -< B -< C
A -< B -< C -< D -< E
A -< B >- C
Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
Essentially yes.  The section records will be 100% unique for each project.  As far as duplication, every time we bill the customer (typically monthly), we will create a "billing" child record for the Project.  The 'sections' are added to each 'billing' record via a merge table that is related to both.  That merge record, which joins the 'section' record and the 'billing' record is where we add the % complete for the individual section for each individual month..  

If you're familiar with Schedules of Value and AIA billing from your days in the flooring industry, that's what we're duplicating here.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
I see that Dan has chimed in.  Can you answer Dan's question we are still not able to understand your table structure relationships, and an short example would be helpful of a Project with say 2 sections and then explain what needs to happen when you go to Bill the first month.
Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
Dan,

Projects -< Sections
Projects -< Billings

Sections -< Billing_Section_Merge
Billings -< Billing_Section_Merge
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I love nomnoml:

http://goo.gl/vpVYj2
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
We should just post the nomnoml "markup" in the post and have the forum convert it to a cheap ER diagram:

<nomnoml>
#padding: 12
#spacing: 120

[Projects] 1 - n [Sections]
[Projects] 1 - n [Billings]
[Sections] 1 - n [Billing_Section_Merge]
[Billings] 1 - n [Billing_Section_Merge]
</nomnoml>

UPDATE:

http://goo.gl/MtdegU

<nomnoml>
#padding: 12
#spacing: 120

//Relationships
[Projects] 1 - n [Sections]
[Projects] 1 - n [Billings]
[Sections] 1 - n [Billing_Section_Merge|Related Section]
[Billings] 1 - n [Billing_Section_Merge|Related Billing]

//Key Fields
[Projects|Record ID#]
[Sections|Record ID#|Related Project]
[Billings|Record ID#|Related Project]
[Billing_Section_Merge|Record ID#|Related Section|Related Billing]
</nomnoml>
Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
Project A has two child sections, Section 1 and Section 2.  Section 1 includes a 'section price' field of $100, Section 2 is $200.  The total value of Project A is now $300 as it is the sum of all child sections.  

At the end of January we create a Billing record for the Project for that month.  The billing record has very little data in it.  Once we create that billing record for that project for January, we add a merge record to January's bill for Section 1 with a % value in the merge record of 10%.  We then add a merge record for Section 2 with a % value of 15%.  

What this implies is that our January bill should be composed of 10% of Section 1's total value of $100 ($10), plus 15% of Sections 2's total value of $200 ($30).  The total bill is now $40 for the month of January.  These merges with % are what generates the amount to bill the customer.  

In the above example there are only two sections.  In the real world, most of our jobs have anywhere from 20-50 sections depending on the scope, and there are dozens of jobs that need billed each month.    

What works very nicely with this arrangement is that it is very simple via summary fields to know how much of each section has been billed, how much is outstanding, etc.  It also allows us to summarize the billing / financials of the job for both ourselves and our customers based on any combination of months / sections / total project etc.

In other words it works great from a technical standpoint, but is lousy from a user's standpoint as they have to click that "add section_billing" merge button for every section of every project every month.  We have experimented with rules and warnings that prevent them from adding the same section twice to the same bill, and warning them if they miss adding a section to a bill, as all sections in a project should be included in all bills for that project every month.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
There is a way to do this natively using API_RunImport.  Te user would create the monthly billing record and push the magic button to create all the merge records.  But I have to go roller blading before it gets dark.  Back on line later .....
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
OK, "no problem"

So basically, we want to push a button on a monthly billing record and create a record in the merge table for each section the project has.

The URL formula button will update [record id#] = 1 in a Focus Billing Record table to load it with the Focus Billing Record's [Record ID#]  and the Focus Project's [Record ID#].  It will then do an API_RunImport to create the needed record then the it will refresh the Billing record so the user can see the child merge records did appear.



Make the Table called Focus Billing record, add two numeric field for Focus Billing Record ID# and Focus Project Record ID#.

Add 1 record and then change all the Role Permissions so no one, even you, can add any more records, but any Role can edit.  It will be Record ID# 1

On the Sections Table make a formula numeric field called [Link to Focus Billing record] with a formula of 1. 


Make a relationship between the Focus Billing record table and the section table based on that reference field and lookup the Focus Project Record ID# as well as the Focus Billing Record ID#


Just this once, hand edit the Focus Billing Record ID# 1 record and enter a value for a Focus Billing Record and its  the Focus Project


Next, Go to Import for the merge table and choose table to table copy.

Start building a saved Import into the Merge table where the source table will be Sections.

Set the filter "where Related Project = Focus Project Record ID#"

Map the Record ID (ie of the Section table) into the field [Related Section] on the merge table
Map the Focus Billing Record ID# into the [Related Billing record]

Save.

Run the import by hand.

You should then be able to see if it worked and the correct # of sections copied across into the merge table for the billing record you hand entered as your focus
Next make the formula URL to automate this

var text SetFocus = urlroot() & "db/" & [_DBID_FOCUS_BILLING_RECORD]


& "?act=API_EditRecord&rid=1"

& "&_fid_6=" & totext([Record ID#])

& "&_fid_7=" & totext([Related Project]);



var text RunImport= urlroot() & "db/" & [_DBID_ of the merge table] & "&ID=10";

var text RefreshBillingRecord = urlroot() & "db/" & dbid() & "?a=dr&rid=" & totext(record id#]);

 $SetFocus & "&rdr=" & URLEncode($RunImport)

& URLEncode("&rdr=" & URLEncode($RefreshBillingRecord))









Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
Mark,

First, Thanks so much for the help.

The import is running correctly when run manually.

The formula URL field is causing me issues.  It is correctly updating the two fields in the 'Focus Billing Record' table, but after that it is not executing the import / redirect.  We are getting the quickbase error page "there is nothing there . . . ".

I've confirmed that the correct DBID is being referenced in the RunImport statement as well as the ID of the import itself.  Can't seem to figure out where the formula URL is breaking down . . .
Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
Mark,

Never mind, we figured it out.  What was missing on the URL to run the import was "?a=API_RunImport".  After adding that in, it's working like a champ.

The finished formula URL looks like so:

var text SetFocus = URLRoot() & "db/" & [_DBID_FOCUS_BILLING]

& "?act=API_EditRecord&rid=1"

& "&_fid_6=" & ToText([Record ID#])

& "&_fid_7=" & ToText([Related Project]);



var text RunImport= URLRoot() & "db/" & [_DBID_SECTION___BILLING_MERGES] & "?a=API_RunImport&ID=10";

var text RefreshBillingRecord = URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & ToText([Record ID#]);

 $SetFocus
& "&rdr=" & URLEncode($RunImport)
& URLEncode("&rdr=" & URLEncode($RefreshBillingRecord))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Oh, right, Ii see now i forgot the actual run Import in my post!

It's very empowering to be able to mass create records with native QuickBase.  Good for you!  

Btw, until about 4 months ago there was what I felt was a bug on API_RunImport which prevented normal non admin users from running API-RunImport unless they had the high level permission to edit field properties. letting regular users edit field properties is very dangerous, of course.   I lobbied hard and they did finally come though with a bug fix and that is what now allows us to now give regular users such power by just a click.
Photo of Drew

Drew

  • 492 Points 250 badge 2x thumb
Well, completely at Random our import is no longer working.  We were building various summary fields in the related tables, and now the import doesn't run. . . .

Will probably have to put in a support case, as there is nothing in the import which has changed, and the referenced fields have not changed.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Does the import run when you run it manually?  What is in that magic record ID=1 record for the Focus Billing record and the Focus Project?  Is the button correctly populating the correct _fid's_ on that focus record?
Photo of Steven

Steven

  • 0 Points
The button is populating all fields correctly and the magic record is correct. It does not run manually nor does it run with the formula URL button.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
What happens when you click the Import button manually?  No error message, no indication that it tried?  No feedback at all?
Photo of Steven

Steven

  • 0 Points
The page "loads" and tries to do something but no records are imported. No error message. No feedback.

Drew and I ran into this problem yesterday. Today I went in and deleted all the tables involved in the process and rebuilt them. That seemed to work fine. I got it back in working order. However, after spending some time changing labels and cleaning up some forms, I came back to it and tried to run the import through the URL button. It didn't work. Tried running the import manually. It didn't work. It is doing the exact same thing.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Ok,l so you said you have a support ticket in, I think so its either QB support, or you can ask me to look at it for you.  You would need to contact me outside this forum via the information in my profile.  What i would do next is to satisfy myself that the filters that you have for the import are in fact valid.  i would make a regular table report with that same filter to be sure that its catching some records.

Oh!  Btw!  I have run into an import bug where I had a filter with more than 1 criteria.  The import did not import and there was nothing wrong with my filters.  I think it was just a 2 or at most 3 line filter, but I think just a 2 line filter.  i never figured out the problem, so I had to make a formula checkbox field to put those two conditions into one field.

What is your current filter condition(s)?
Photo of Steven

Steven

  • 0 Points
The filter conditions were "Where Related Project is equal to the value in the field Focus Billing Project ID"

I tried the filter on a normal table report and it caught no records. So, I created a lookup field from "Projects" that pulled explicitly the Record ID# for the Related Project. Then I replaced "Related Project" in the above condition with the new lookup field.

Everything is working great now. Thanks so much and I'll let you know if we run into any more problems.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
OK great. thx for letting me know.