Forum Discussion

AndrewRyder's avatar
AndrewRyder
Qrew Cadet
6 years ago

Grabbing a field from a separate table to use in formula

Hi,

I'm wondering if it is possible to use a field from another table (same app) in a formula? I know how to do this with a lookup field once a table-to-table relationship is established. 

I'm creating a job estimation tool that, depending on the department (10) and labor, has different rates, which are subject to change. I could, easily, just have the [field] * 175.0 but I'd like to keep this in a separate table, since we generate proposals from three different tables (types of products). It'd be annoying to change each table when we adjust rates.

Would it require API usage and doQuery? 
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    If I understand the question, the solution here would be to have a table of rates, but they Key field would be a field on the format

    Dept-LaborType

    so for example

    Plumbing-Supervisor
    Plumbing-Helper
    Roofer-Supervisor
    Roofer-helper.

    You can have these be two data entry fields but then a formula field to calculate like

    List("-", [dept], type])

    then a form rule to fill in the key field for the user  

    Then you can lookup that down to your proposal table based on the reference field on ghe proposal record being a similar calculation.

    Then snapshot that field   https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html so you can adjust the rates in that lookup and not change historical records.
  • AndrewRyder's avatar
    AndrewRyder
    Qrew Cadet
    6 years ago
    Mark,

    Thanks for the response. I'm not sure if that would apply to my case. I'll include screenshots of where I am and am trying to do.

    Here is a line item




    Many line items belong to one assembly. Assembly is a boiler plate for similar products we make that contain estimates based on a previous system and work to make a product. An example would be a wood picture frame, but for each client, they're different sizes. Under the costs, I'm grabbing the hours from the table-to-table relationship to the assembly. Those figures are per unit, and my example has 10. 



    So, my intent is to have on each line item, a calculation based off the hourly rates table:



    (I was experimenting with making the line items to hourly rates here to see if it'd work with a lookup, but doesn't)

    For each assembly/line item, certain departments might not be involved, so I leave these blank. I wanted to have the line item display the total calculations to formulate a cost to make a product by department:

    QUANTITY * CNC TIME (PER UNIT) = Cost to CNC
    QUANTITY * FAB TIME (PER UNIT) = Cost to Fab
    etc.

    We break these out by department so they can be tracked against for time reporting within each department.

    So, I could then add all costs from each department up. And, yes, the snapshot option will come in handy since we're doing estimates at the time based on that hourly rate, but those will be increasing and I wanted that to be an option for the folks in sales to modify.

    Is that possible or easy? Worst case, I can just use a formula in the line item field

    Thanks
    • AndrewRyder's avatar
      AndrewRyder
      Qrew Cadet
      6 years ago
      Can I do that if there are separate table entries for each rate? I could consolidate these into one entry. I know I could then use a lookup for this.

      Part of my experiment is a learning exercise, since I figure to be using more relationships and cross-table references in the future. 

      I was hoping there'd be a way to utilize variables from another table without the relationship, something like in a formula.


      Thanks
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      You will need to learn how to leverage Relationships.  It's  foundational to Quick Base's power.

      The usual way to do this is to have a separate entry in a Rates table for each rate type.