Relationship - How can I get around adding a summary field in a relationship if the reference field is a lookup field?

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

Three tables

1. Jobs

2. Production Rates

3. Time Cards

Each time card created has a "reference field-Job Number" that comes from a table called Production Rates. Though this may seem off, There is a reason I have it come from Production Rate rather than the Jobs table. For a reporting purpose, I have created a relationship from my Jobs table to the Time Cards table. The standard relationship works, but I am unable to create summary fields because the reference is Production rate table lookup field called Job Number.

To make things simple.....does anyone know if there is a field I can add to my time cards table, that takes the job number lookup field that is already in time cards, and copies it as a value. Maybe call it Job number 2. I tried just adding one that equaled the other...but it still saw it as a lookup of the lookup. It might need to be some formula that took the absolute value and copied it over. To make things even more difficult, my job numbers have letters and numbers. ( example - 136342-TC)

Thanks in advance!

Josh

Photo of Joshua

Joshua

  • 0 Points

Posted 5 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
I suggest changing your job number field to be called Job Number lookup. Then make a new field call Job Number.

Make a form rule which says
When the record is saved
Change the field job number to the value in the field job number look up

But, of course form rules only work on forms, and not great at it. So you will need to forgo the use of grid edit and in fact to be safe you need to block grid edit on your time card table.

I suggest setting up a safety net subscription report to run every day looking for any records were job number look up is not equal to job number.

He would then have to rebuild your relationship based on this job number which is now a plane data entry field so it will be fine in the relationship and you will be able to do your summary fields.

You can populate the new job number field by using grid edit and doing a giant copy and paste from the job number look up column to the job number column of the grid at a report.
Photo of josh

josh

  • 0 Points
Hello.

 I am having a similar issue with trying to find a workaround for using a lookup as a reference in two separate cases.  Your solution will work for me in one case but in the other a form rule will not work because the record which is performing the lookup is created before the related master record is.  

Here is a better description of my situation:

There are 3 relevant tables in this scenario.  

Assets - Tracks unique pieces of equipment

Asset Log - Contains shipping, maintenance, and return records related to specific pieces of equipment.  

Jobs - Each asset log entry is related to a specific job.  Essentially this is the destination for shipping records, and is equal to Shop for maintenance and return records.  

In addition to the 1 to many relationship where each Asset can have many log entries, there are 2 reverse lookup relationships which allow the Asset table to lookup the Job (location)  entered for the most recent and 2nd most recent log entries for a given Asset.  This allows the Asset table to show both the current and previous Job at which the Asset was located as well as the date of the current and previous log entries.  

In the Assets table I have a report showing a list of all equipment that is currently 'on the job' along with the duration the asset was on the job, the daily rental rate for the piece of equipment, and the rental costs accrued to date.  

I would like a summary in the jobs table that shows the total daily rental rate for all equipment related to that job as well as the rental costs accrued to date for all equipment related to that job.  Unfortunately the related job (titled "current location" in the asset table) is determined using a reverse lookup of the most recent asset log associated with the asset.  

Since the asset record is created prior to the log entry I cannot use a form rule.  Are there alternative workarounds or am I SOL?

Thanks,

another Josh
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
hello "Another Josh"

I suggest that you post this as a new question so it will be seen by more than just me.  You can provide a link to this answer.