Find match for 2 fields in different table, then recognize in 3rd field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
  • (Edited)
I have two tables: task and reports. In each table, I have task numbers. Basically when a task is created, it gets worked on. But in order to assure it is worked, I need to be able to match these two fields in my reports table.

On my reports table, I will import task number field from task page. Once I import, I want QB to match these records by task numbers to make sure they are all accounted for. For this, I wanted to have a 3rd field. This field will be a formula field with a basic if.

So... Is there a way for this to happen?
Thank you!
Photo of Neve

Neve

  • 260 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
May I ask why you are duplicating that task number to a different table?  Can you not do the reporting on that task directly?  I have a feeling the general set up might need re-visited.

Can you describe the actual use case a little more, and your table relations?
Photo of Neve

Neve

  • 260 Points 250 badge 2x thumb
I will try to explain better! Sorry.. It is hard to explain the background of why we need what we need. Two tables currently only have one to many cross-table relationship between the two tables. (If this is not necessary I can get rid of this)

There are 2 tables with different fields (only a few related fields) for different roles. So I guess to explain what I need is...

Basically, I want to be able to look-up if that field 1 value exists in field 2. And if that value exists, then I want to create a formula checkbox for "if [task number 1] = [task number 2], TRUE, FALSE"

What I am picturing is a table with three fields. First would be stocked task numbers, followed by reported task numbers. Then a "Purchased" formula checkbox. To give you a visual idea, here is what I am picturing in my mind.

  • Field 1[Task Number_Stock] || Field 2[Task Number_Report] || Checkbox [Purchased Item]
  • 2017-ABCD || 2017-ABCD || Checked
  • 2017-XYZ || <> || not checked
  • 2015-DEF || 2015-DEF || Checked
Hope this gives a better idea/picture of what we need....
What I would do here is build a formula checkbox field on the tasks table to tell you if that task has been reported (a child record was added)


Assuming your relationship is set up such that 1 task record can have many report records, you can create a summary field that counts the number of child report records have been created.


Then, on the task table, your formula checkbox field formula would be something like: if([# of reports]>0,true,false)


Let me know if that was helpful.
(Edited)