Show report link only if records exist(no table relationship)

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • In Progress
I have an audit log for when poles are taken are taken out of or put in a work request. I have 3 tables associated with this: Work Request table, Poles table, and Audit Log table. The audit logs capture "Old WR" and "New WR."

The table relationships I have are between the <Work Request table and the Poles table> and then between the <Poles table and the Audit Log table> referencing related pole#.

The Poles table form report links to all the logs with the related pole. The Work Request table form has two report links in their own section called "Pole History" - the first showing poles added to the Work Request three days after the initial work request creation and the second showing poles taken out of the Work Request.

My question is: How can I collapse the "Pole History" section when there are no records in the report links? I know I will have to use form rules for this, but report links aren't values, so I don't know how to set up the criteria for hiding the section.
Photo of Jordan McAlister

Jordan McAlister

  • 1,360 Points 1k badge 2x thumb

Posted 5 months ago

  • 0
  • 1
You have not explained in your relationships as to which tables are the One and which are the Many. But you will need to have a relationship and a summary field to count the number of children. Then hide the report link field if there are no children.
Photo of Jordan McAlister

Jordan McAlister

  • 1,360 Points 1k badge 2x thumb
Work Request table is the parent table of the Poles table and then the Poles table is the parent table of the Audit Logs table

key field of Work Request:WR#
key field of Poles: Pole#
key field of Audit Logs: built in record id

so, I need to make a relationship between Audit Logs and Work Requests? Would I need to make two relationships in order to make the Old WR and the New WR the reference field then make summary fields in each to count the Old WR and the New WR fields within the Audit Logs table?
Photo of Jordan McAlister

Jordan McAlister

  • 1,360 Points 1k badge 2x thumb

Here's an example of the current setup. This is within WR 70890371 form. It will show poles taken out and poles put in. So if these poles, instead of getting no new replacement WR, were put in let's say WR 69123163, the same log record would show in WR 69123163 but in the poles added to WR section.

You do not need to create any extra relationships.

Do a summary of the #of Audit records on the relationship between Poles and Audits.
Then summarize that summary field up to the work order and use that field in your form rules.
Photo of Jordan McAlister

Jordan McAlister

  • 1,360 Points 1k badge 2x thumb
I did like you suggested, and it works partly. It only summarizes the total number of logs based on the poles in the current WR, so if 2 out of 5 poles in a WR have logs and are listed in the WR as being added in after the initial WR date it'll summarize those, but it doesn't summarize any poles that were taken out of the WR and either put in another WR or not given a replacement WR. 
Below is an example of when there were only poles taken out of a WR.

Will I need to use the "Old WR" field in order to make this work. Currently, that field is just a simple text field that gets it data from a quickbase action that takes the old WR data to overwrite it.