Forum Discussion

ZacharyStein's avatar
ZacharyStein
Qrew Member
2 months ago

Lookup a value with multiple conditions into a field in a separate table

I have a table called "Forecasts" with many different forecasts at different time periods.  I want to create a field in another table that pulls a specific value from the "Forecasts" table into a field called "Original Forecast".  The criteria for the "original forecast" field would be: Year = 2024, Forecast Type = Original, and the Column Name = Yearly Forecast.

Can anyone point me in the correct direction?



------------------------------
Zachary Stein
------------------------------

3 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    2 months ago

    Is the secondary table related to Forecasts? You can use native summary fields to pull the value in question using those constraints as the conditions, and then set 2024 as a variable or global setting, or based on the current year if it's rolling



    ------------------------------
    Chayce Duncan
    ------------------------------
    • ZacharyStein's avatar
      ZacharyStein
      Qrew Member
      2 months ago

      Thank you so much for your reply!

      Yes the tables are related.  It is a many to one relationship where the forecasts table is many.  When I create a new field what field type would I use?  I don't see an option for summary fields. 



      ------------------------------
      Zachary Stein
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        2 months ago

        You don't set the type for a summary field - it's determined automatically based on the type of data you're summarizing. So if lets say you're summarizing the 'total forecast' it will likely auto-determine that is a numeric field and create accordingly. 



        ------------------------------
        Chayce Duncan
        ------------------------------