Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 12, 2021 04:33 PM
Greetings! I’m managing forecasts and actuals in a base with multiple tables. We built all the budget line items for each project in Table A. For example, forecasts for Q1 Project Sock has wage = $10k, prof services = $20k, and contract labor = $55k. Basically a table with each column as a PnL item. We have a separate table and view for finance partners to complete the actuals against these forecasts. However, to avoid having 50 columns and causing fatigue, we have Q1 Budget as a column. As such, the individual line items show the project total ie wage = $85k, prof services = $85k, contract labor = $85k. How can I connect or create a formula that pulls from Table A only the line item amount into the finance partner view line items? Thank you!
Nov 19, 2021 01:41 PM
Welcome to the community, @Sarah_Brinker! :grinning_face_with_big_eyes: Some elements of your design are unclear based on your description. Are you saying that all of the line items in [Table A]
are linked to a single record in [Table B]
?
From the sound of it, you could probably do what you want in a single table using multiple views. One view would show a {Forecast Amount}
field with your projected total for each line item. In another view, that field could be hidden and a {Actual Amount}
field could be visible, where actual amounts could be entered for the same records. Or if the forecast amounts need to remain visible, you could just lock that field so that the value can’t be changed as the finance partners do their thing. Does that sound doable?
Nov 23, 2021 07:02 AM
Hi @Sarah_Brinker,
To echo what @Justin_Barrett has said, it does sound like you could utilize views to set things the way you want. I do have to ask though, would you need to compute any values like summing for Q1 for each item in Table A? If so, it may be worth it to have 2 tables and utilize Linked records. If you like, I’d be happy to have a short discussion with you to understand your use case better and give ideas and lessons learned for things I’ve set up for other companies.
Thanks,
Chris