Is it possible to have a User List populate from a field in another table?

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

I have two tables, Labor Invoices and Labor Inv Lines, related such that a Labor Invoice has many Labor Inv Lines.

In the Labor Inv Lines table each labor invoice line has a project manager assigned to it who needs to approve the labor invoice line. For the purposes of this question lets say that project manger is a User Field.

In the Labor Invoice table I would like to create a field that contains all project manager Users via a User List Formula in the Labor Inv Lines table ( consolidated list of project manager Users that still need to approve a line on that invoice ).  So far I have found that this does not seem possible  with a Summary field which would have been very easy. I have been unable to find a working solution.

The ultimate goal is to have a report in the Labor Invoice table that uses the list of Users from the labor invoice lines table to flag project managers that have labor invoice lines awaiting approval. Once a project manager approves their portion of the invoice ( associated labor lines on the Labor invoice that only that User is assigned to ) it falls off their report.

I am unable to just create the report in the Labor Inv Lines table as we attach the Invoice PDF as a file attachment on the Labor Invoices table and QB does not allow links to file attachments that I am aware of.  Additionally, dragging applicable project managers into the labor invoices allows for them to grid edit the summary report of labor invoice lines and allows for a quick way to edit\approve applicable lines to them and also have the applicable attachment Invoice PDF open for reference.

I appreciate any suggestions. Thanks!

Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,558 Points 50k badge 2x thumb
There are two solutions here. I will post the easiest one first 

If you want to display a file attachment link on a child record you can use this URL formual



URLRoot() & "up/" & [_DBID_put the name of the table dbid here] & "/a/r" & [Related Parent] & "/e30/v0"


In the formula above, change the 30 to the field ID number of the file attachment field on the parent record.



There is a way to float up a bunch of Project managers up to the Parent record, but that is lot more work.  So my suggestion is to simply add that link to the child record and the the managers just see a clean list of the specific line items they need to approve.   If they choose to want to see the whole invoice, you can provide a link for them to see the Parent record.
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
Thank you for the reply Mark!
I have implemented suggestion one with the URL formula and it works well. Ironically the applicable field ID for me was already 30.

If you wouldn't mind, I would like to pursue the second option you indicated would be more work.  This particular situation will affect me in another two tables and some folks in my group are pretty set on the approach I explained as far as our long term process is concerned.  I would appreciate that info or a link to the solution in a previous question you might be aware of.

Thank you for your time on my question and the numerous others you help every day.  I always find your responses helpful when I am browsing the boards here.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,558 Points 50k badge 2x thumb
Wow, it was fid 30 already?  That is freaky.  A good omen I guess.

Well here is the long answer at to how to float up unique values form a child table.


1. Make a Summary Minimum Record ID# of the Invoice Lines Table.  Call it [Record ID# of Invoice Line of Project Manager 1]

2. Make a reverse relationship where 1 Invoice Line has many Invoices, but on the right side use [Record ID# of Invoice Line of Project Manager 1].

3. Lookup the userid to the Invoice table and call it [Project Manager 1].

4. Go to the normal relationship and lookup [Project Manager 1] down to the invoice Lines table, and call it [Project manager 1 of all Invoice Lines]

5. Back on the normal relationship make a new Summary Minimum Record ID# of the Invoice Lines, but this time subject to the filter that the  [Project manager is not equal to the value in the field  [Project manager 1 of all Invoice Lines].

6. New reverse relationship to get the Project Manager looked up to the Invoice Lines table and called [Project manager 2].

7. Look that up down to the invoice lines table and call it  [Project manager 2 of all Invoice Lines]



repeat thew process but for each repeat add an other filter, so that the Minimum Record ID# is not the one where the [project manager] is not equal to the [project manager 1 of all invoices]

and 

[project manager] is not equal to the [project manager 1 of all invoices]

You will need to decide on the practical number of loops to make.  ie what is the most number of different project manager on a single invoice that the system needs to handle.
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
Hello Mark, I tried working through the list but am stuck on step 5.  While creating the Summary Min Record I am unable to filter for "Related PM, is not equal, to, the value in the field" as [Project Manager 1 or all invoice lines] is not in the drop down.  Are there certain field types that cant be filtered against?  Any thoughts as to where I might have tripped up or what I can do to dance around this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,558 Points 50k badge 2x thumb
The test is not on [Related PM]. It's on the [Priduct Manager userid] field
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb
That helped thanks, It works perfect!
 I have a separate Project Managers table where I was pulling the User ID from, unfortunately I set that field up as a User List and not a User field so that is why I was unable to create the filter.  I created the proper field type in the PM table and pulled it down.  Then I deleted my relationships and fields and started over with your 7 steps list.   

I added in an additional filter for Approval Status is not equal to Approved on each Summary Minimum so essentially the PMs vacate the hold on the lowest line as they approve each Labor line.  This allows for the Approval Needed report to clean itself up as PMs approve lines.

I cant thank you enough for your time on this!  I am already thinking of ways to use this approach in other places.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,558 Points 50k badge 2x thumb
Thx for letting me know.  Reverse relationships are very tricky the first time around.  QuickBase Developers are aware of this and are looking to make the process more stupid simple in future releases - maybe who knows a year away????