Forum Discussion

JoshuaRumbut's avatar
JoshuaRumbut
Qrew Trainee
7 years ago

Associating new records to existing records based on category and in order

Hi, I've looked high and low and can't find quite what I'm looking for.

A user shows up and fills out a form, for simplicity lets say they enter a single category and submit. In a separate table I have many items for each category, that must be associated with the prior submission in a very specific order. For instance, if this is my items table:

  1. "Animal"
  2. "Mineral"
  3. "Vegetable"
  4. "Animal"

The first request for animal must be associated with item #1, and the second request for animal must be associated with #4.

Ideally, when the user submitted the request form, Quickbase would go and make the association on its own and report the item number back to the user. I haven't found any way to do this. The other acceptable solution would be for the user to have something like the related record dropdown, but with only the correct item on it. I'd be OK with that.

What I have currently is this formula:

URLRoot() & "db/" & [MY_DB_ID] & "?a=s&query={'12'.TV.'" & URLEncode([Category]) & "'}&opts=disprec.num-1"

This links the user to a one item report that appears to always show the correct item, but then the user has to click edit and manually link the Request to the Item, which is cumbersome and leaves room for error.
Ask about Quickbase functionality
Forms
Formulas and functions
  • KevinSlider's avatar
    KevinSlider
    Qrew Trainee
    7 years ago
    Joshua, I need a little more information, but I think Automations may be what you need.

    1. User adds/edits an "Order" record, selects a category, Save
    2. Automation detects Category is selected on add/edit, Adds items that belong to that category to that "Order" (this can be the tricky part, and I would need more details to solve)

    Automations occur in the back end After a record is saved, so a refresh may be needed to see the automated actions. 

    Let me know more about the architecture of the DB, and pursue automations in your application.
    • JoshuaRumbut's avatar
      JoshuaRumbut
      Qrew Trainee
      7 years ago
      Hey Slider, thanks for the advice, I'm going to give Automations a shot and let you know. I'm new to Quick Base but old(er) to databases. Feature discovery is definitely something I'm finding tricky in Quick Base, it appears to take some time to learn what is possible and what isn't.
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    7 years ago
    Hi Joshua,

    The second solution you mentioned is possible for setting up a custom report so that when your users go to pick a Related Item they are only shown Items that haven't already been picked. I have done this before when a builder wanted unique ids for training events. 

    You can do this by creating a summary field between your two tables so that you summarize the number of records that are related to each Item. With that summary field you can then make a report on the Items table that only shows Items that have <1 related records. This way you are only shown unassigned items on the report. Then you can set this report on your form for the Related item field, this way once a user selects an item and saves the record it will disappear off the report but later on if necessary you can find it as the Admin and even add more related records if you had a need to. It does require the user to pick a related Item and if they use a Grid Edit you would want to make sure to set your reports to use the same form to prevent them from getting around the report filtering. I hope that is helpful. 
  • JoshuaRumbut's avatar
    JoshuaRumbut
    Qrew Trainee
    7 years ago
    Hi Evan, thank you so much. I tried basically what you mentioned (including the grid edit) and I thought it worked great and was exactly right but it seems to have not gone over as well as expected with the client. Still awaiting the reason why that was, may have been due to field names/demo data rather than the actual functionality.