Forum Discussion

RyanParr's avatar
RyanParr
Qrew Assistant Captain
9 years ago

1 to 1 Relationship

Can Quickbase support a 1 to 1 relationship?

I have an application that consists of 2 tables, Orders and Equipment.  Orders are created by hand while Equipment is a predefined list of records i.e. Dell Computer Serial Number 1234, Dell Computer Serial Number 1235 etc.

An Equipment record can only be associated with a single Order but an Order can have many Equipment.  (The current relationship is that an Order can have many Equipment.)  But.. the Equipment table should be static data, the user should not have to create an Equipment record each time they are creating an Order record. Rather than Add an Equipment record from the Order page i'd like to link or relate an Equipment record with an Order, is this possible?
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    9 years ago
    What you would usually do here is have an orders table, and equipment table, and a line items table, where an order can have many line items, an equipment item can have many line items. 

    From there you can add a quantity field to the line item record, and a total field that takes the base equipment price multiplied by the quantity. 

    Delete your existing orders to equipment relationship and give the above a shot. it is the standard way of handling what you are describing. 

    good luck!
    • Eric_J_Hansen's avatar
      Eric_J_Hansen
      Qrew Cadet
      9 years ago
      Ok - that being said, I think you would still have the same tables. I laid out above. However, once an equipment has a child 'line item' record, it would not show up in the list for the next time you add a line item to an order. You still need a place to store these unique equipment assets. And, in order to link many of these unique equipment assets to one order, you need a join table. 

      So to implement this, create a summary field on the equipment table that counts the number of line items related. Then, create a formula field (text or checkbox) that has a formula something like: if([# of Line Items]=0,"Available","Issued") where if there line items is equal to 0, it is available, if it is not equal to 0, it has been issued. 

      Then create a report labeled 'Available Equipment' I would recommend only putting 1 to 3 fields on that report. Add a filter to that report: where [# of Line Items]=0. This report will be your active equipment list for use on orders.

      Now go to your line item table form, and on the form element for the equipment reference field, have it display choices from your 'Available Equipment' report instead of the standard record picker fields. 

      That should do it.
    • RyanParr's avatar
      RyanParr
      Qrew Assistant Captain
      9 years ago
      But each equipment only has a quantity of 1.  All equipment is unique, there's not a pool of a single "Equipment".
    • RyanParr's avatar
      RyanParr
      Qrew Assistant Captain
      9 years ago
      That did it, you nailed it.  Thank you Eric!