I have 2 tables with very similar data. One is specs of products we've sold, another is products we use as replacements. I need a search where I enter sku from table 1 and it matches the specs of that item to the specs of the comparable items in table 2

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
Photo of Nanci A

Nanci A

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
Can you give some more specific examples of what constitutes a match in the specs. How many spec fields are there for example. Will the fields match exactly?
Photo of Nanci A

Nanci A

  • 0 Points
There are 6 different fields that must be compared. Some are straight matches, 2 are numeric Greater than or Equal to
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
One more question. Do we need to support multiple concurrent users who might bump into each other or are there just a small number of people using this matching query at any one time.

I'm tied up right now (20 cm of new powder) but I can post an answer later tonight Pacific Time Zone.
Photo of Nanci A

Nanci A

  • 0 Points
Multiple users could be searching concurrently. We've got about 30 people in the application, but unlikely that more than maybe 5-10 would be searching at the same time with. Thank you for your help!
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
No problem.......
The goal will be to create a button on the Sold table records to query the Replacements table for candidate replacements.
The user would locate the Sold item and "launch" a report off that item.
1. Create your perfect report on the Replacements table with <ask the user> filters for all 6 specs, so 6 <ask the user> questions to be answered..
2. Run the report and answer all the questions and then observe the URL. It will be long so copy it off to a notepad or Word.
3. Observe the format. It will look like

www.quickbase.com/db/xxxxxx?a=q&qid=38&nv=6&v0=answer1&v1=answer2&v3=answer3 etc .....

So while you could have your staff locate the Sold item, and write down the search criteria and run the report and answer the 6 questions, you know we can do better. The Sold record has all the answers to the questions, so let it answer them.

Create a URL formula field with a formula like this. I will comment it as I go with // comments

URLRoot() & "db/" & [put the table ID here in square brackets]

// the first part will make the lead in part of the URL and then obtain the table ID from the very bottom of advanced settings of the Replacements
// Table. So the square brackets might be [_DBID_REPLACEMENTS]

& "?a=q&qid=xx"
// tell it to run a query and the number of the report that you built in the xx.

& "&nv=6"
// your query has 6 <ask the user> questions

& "&v0=" & urlencode([spec1])
// so the answer to the first question which quickbase numbers as question 0 is the field name in the Sold record with the first answer.
// You need to URLENCode it because it may have spaces or illegal characters which URLs don't appreciate.

& "&v1=" & urlencode([spec2])
& "&v2=" & urlencode([spec3])
& "&v3=" & urlencode([spec4])
& "&v4=" & urlencode([spec5])
& "&v5=" & urlencode([spec6])

Here is an example from one of my apps where i just had one <ask the user> question

URLRoot() & "db/" & [_DBID_ITEMS] & "?a=q&qid=38&nv=1&v0=" & [Cost Centre]

Thats it.

Be sure when you create that report to give it a name like Used in a URL Query Formula, and I would make it not available to any Roles, so no one, including you, is tempted to delete it or accidentally use it for another purpose and modify it. You will give you button a name such as Show Replacements and decide if it should show as a button or just as a link.
Photo of Nanci A

Nanci A

  • 0 Points
This is exactly what I needed! Thank you for the help!