Skip to main content
Question

Grabbing a field from a separate table to use in formula

  • July 11, 2019
  • 0 replies
  • 50 views
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? 
    This topic has been closed for replies.

    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
    • Author
    • Quickbase Alumni
    • July 11, 2019
    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
    What is stopping you from looking up the Rate for Prep from the Rates table?
    Terms & ConditionsAccessibility statement