Forum Discussion

NeveLeem's avatar
NeveLeem
Qrew Trainee
9 years ago

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

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!
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    9 years ago
    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?
  • NeveLeem's avatar
    NeveLeem
    Qrew Trainee
    9 years ago
    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....
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    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.