Skip to main content
Question

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

  • February 25, 2017
  • 0 replies
  • 28 views
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!
      This topic has been closed for replies.

      • MCFNeil
      • Quickbase Alumni
      • February 25, 2017
      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
      • Author
      • Quickbase Alumni
      • March 7, 2017
      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....
      • EricJHansen
      • Quickbase Alumni
      • March 7, 2017
      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.
      Terms & ConditionsAccessibility statement