Forum Discussion

ScottGrund's avatar
ScottGrund
Qrew Cadet
8 years ago

Passing data back to a child record from parent table in order to filter the list of records available to relate to parent

Okay, hopefully I can explain what I need here. We build records in a table called defects. These are reported problems that need to be fixed by work orders. The defect records sit in their own application. Work orders are developed in the other application and have the work order table set up as a parent to the defects table. When we start a new work order, we select one or more defects to fix with the new work order. I need to flag each defect that becomes assigned to a work order so it drops off the list for the next selection of a defect to be assigned. I've tried creating a lookup field (checkbox that goes to true when a defect is selected for the work order) from work orders in the defects relationship, but cannot get the field to populate in the newly attached defect record so it will be filtered out from the to be assigned list.
  • This should definitely work.

    I would set up a formula checkbox field called [Work Order Exists?] with the formula of

    true

    Look that up to the defect.

    Then when selecting a defect use a report that filters where [Work Order Exists?] is not checked.
  • I must be placing these fields in the wrong place. I have a field [defect assigned] in the work order with defects form that changes to "Yes" when a defect is added. This creates the record in the join table of the many to many relationship Work Orders > Work Orders with Defects < Defects. Then I have this Defect Assigned field as a look up in the Defects relationship. Then my [Work Order Exits] formula field looks at the Defect Assigned for the "Yes". Problem is, the "Yes" does not pass through to the defect record.
  • Based on that relationship setup, I think that you need a summary field on the Defects table to count the number of "work orders with defects" where [Work Order exists?] is checked.  Then you can filter a report of defects where that summary field = 0.  Make sure you set that summary field to treat blank as zero.
  • It took me a bit but you sent me in the right direction! Because I am crossing apps I had to place that summary field in the Work Orders with Defects side of the relationship then use that field as a lookup in the Defects side. Success! Thanks again...