Forum Discussion

joevandervest's avatar
joevandervest
Qrew Cadet
2 years ago

Sub-Select in Query ?

I'm trying to generate a list of items in one table that don't exist in another table based on query parameters.  Intent is to create a system whereby a clerk can check on availability of an item between certain date parameters.  

Roughly an SQL would look like this: 

SELECT ItemID
FROM Items
WHERE ItemID NOT IN (
    SELECT ItemID
    FROM LoanedItems
    WHERE Start_Date >= '2023-01-01' AND End_Date <= '2023-03-31'
);

I'd like to be able to do this interactively on the fly;

Anyone got any ideas how to do this, I'm somewhat stumped. I don't believe a query formula can be used this way, and it might have difficulty with returning data set; i thought about generating a report to show items loaned in the period (positive logic) and show it in a window for the clerk - but that would require human processing to know what items AREN'T in the list to figure out what is available....

I thought about a pipeline, but how can i TRIGGER it without forcing a record save....

Thanks for any ideas ... 



------------------------------
joe vandervest
------------------------------

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    I am trying to understand what you're asking for here.


    You are saying you have a list of items. And you want to know which of those items is not in another table. So can't you just have a Formula  Query Checkbox  on each individual record to check if that particular item is in The other table or not. 



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • joevandervest's avatar
      joevandervest
      Qrew Cadet
      2 years ago

      Thanks for responding Mark. 

      The context is a "reservation" type system, so I'll want to query a transaction table of reservations (past, present, & future). There may be many transactions for each item.

      I'll want to query the item table to find items that are not listed in the transaction table for various reservation days provided for start and end. 

      I am now opting for a little bit of a messy solution using pipelines, a special table, some interesting relationships, and query formula fields to determine availability for the given time frame. 

      It's messy and inelegant, but I think it'll work. I'll let this gestate a couple of days and attempt to verify the approach then. 



      ------------------------------
      joe vandervest
      ------------------------------
    • joevandervest's avatar
      joevandervest
      Qrew Cadet
      2 years ago

      Resolved 

      Ugly as it is, I was able to solve this problem.  The challenge was to create a rental screen that could be used to select a type of rental based on start/end dates, and use these to generate a list of items that were available for the desired time period. 

      Here's roughly how i did this - (note this solution may not scale really well, but at my low transaction and data level it works fine):

      --- User provides type of equipment and start/end dates for borrowing the device type using a request screen. 

      --- When saved as an inquiry this triggers a pipeline to copy the list of inventory items for that item type. 

      ---- The generated list of inventory items contains query formulas that examine all existing transactions and reservations to decide if this item for this request, based on the request dates, would be available. 

      ---- using a selector that provides ONLY  a list of items available for the requested date period, pick the individual item to be rented/loaned 



      ------------------------------
      joe vandervest
      ------------------------------