Forum Discussion

RickLindner's avatar
RickLindner
Qrew Trainee
6 years ago

Display query results on form?

Hello, I have table of inventory items and a table of customer service records.  Typically, an inventory item may have many customer service records however the data is not related (nor do I want to go down that path).  That said, an inventory item does have several values that are commonly referenced in customer service records, so the data can be correlated. 

For example, an item may have a UPC, an Item Number and a MFG Part Number.  The customer service record may reference one (or all) of these values in a note field. 

I am looking for a way to display a report of correlated customer service records on the inventory form.  In plain English, I want to open the detail form for inventory items and have a tab that displays a report of customer service records where either the UPC, or the Item num, or the MFG Part num is contained in the Notes field.  

If I only wanted to compare one value from inventory to a customer service record, I could use a report link and deselect Only include values if they match exactly.  Since I want to compare several values, I'm not sure how to proceed. 

Any ideas? 

Thanks.  

Rick​

------------------------------
Rick Lindner
------------------------------
Forms
Relationships
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    I see three choices.
    1. Have three embedded reports. But, extra space that may be blank and you could get a sort of mismash of duplicate  or even triplicate Customer Service records across the three reports.

    2. Have a button to click to run the single report. I can help you with the button syntax, but the problem there is that the report may be blank, so the user experience is not so good to click a link in the "50% hope" that there will be data to see that matches.

    3. There is a way to make a button on the Inventory form put that record in focus. It would be called Show Customer Service records.  That will then quickly refresh the record and the focus button will disappear because the record is already in focus. But now that the Inventory record is in focus, a form rule can now show the embedded report of all customer service records which match on any or your three fields.  The way this can work is to record the focus inventory record on a helper table that then can be looked up down to the customer service records.  If this is a low volume type activity with few simultaneous users, the helper table can just be a single record table. B ut if its many users, it would better to go with a User Focus concept where the Key field of the helper table is the Userid.  If you like that option I can help set that up with you.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • RickLindner's avatar
      RickLindner
      Qrew Trainee
      6 years ago
      Hi Mark, sorry it took me a few days to get back to you.  I'm not worried about the report returning no records or potentially pulling unrelated records.  There will only be a few users who can see this tab, and the idea is to show any records that may be related.  

      I vaguely remember embedding a report on a form where the report had an "ask the user" type filter but instead of prompting the user, the report was somehow passed a search value from the current record, or form?  Does that sound familiar to you at all?  

      We can take this offline if you like.  It may be easier to show you the data.  

      Thanks, 

      Rick




      ------Original Message------

      I see three choices.
      1. Have three embedded reports. But, extra space that may be blank and you could get a sort of mismash of duplicate  or even triplicate Customer Service records across the three reports.

      2. Have a button to click to run the single report. I can help you with the button syntax, but the problem there is that the report may be blank, so the user experience is not so good to click a link in the "50% hope" that there will be data to see that matches.

      3. There is a way to make a button on the Inventory form put that record in focus. It would be called Show Customer Service records.  That will then quickly refresh the record and the focus button will disappear because the record is already in focus. But now that the Inventory record is in focus, a form rule can now show the embedded report of all customer service records which match on any or your three fields.  The way this can work is to record the focus inventory record on a helper table that then can be looked up down to the customer service records.  If this is a low volume type activity with few simultaneous users, the helper table can just be a single record table. B ut if its many users, it would better to go with a User Focus concept where the Key field of the helper table is the Userid.  If you like that option I can help set that up with you.


      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      markshnier2@gmail.com
      ------------------------------