Newbie here - lost in copying sum from a table and inserting that sum into another table

[Nico_Stanculesc]

I will start with apologies as I am new to this and struggling to reconnect my excel brain to a database one…

so, I have a table with several entries, grouped (summed up) by a “type”. i have sums (formulas) for each of these records (types) which i need copied to another table…

in my excel brain, i use +CELLFROMDIFFERENTTableORTab so is there something similar?

can someone take pity of me and show me how to do this, please?

gratefully -

nico

PS: Yes, I know I should sit in through the basics some more. Clearly, homework not done.

[Justin_Barrett]

Welcome to the community, @Nico_Stanculescu! :grinning_face_with_big_eyes: Unfortunately you can’t arbitrarily link fields between tables. To connect tables together requires a link between records (not fields). One possible way to do this is to use the {Type} field you’ve already built. If this field is a single select, I suggest changing it to a link field pointing to your summary table. You didn’t give much detail on your setup, so I’m going to use this example:

Say you’re an auto dealership, and you’re tracking vehicles sold by type: car, truck, minivan, SUV, etc. With each of these types represented by a single record in a [Summary] table, and each vehicle record in the [Inventory] table linked to its appropriate type in the [Summary] table, you can fairly easily assemble a summary of profit, expenses, etc. by type using rollup fields.

A rollup field does just what its name implies. It lets you roll up (i.e. collect, summarize, etc.) values based on linked fields.

Going back to the dealership example, you could make a rollup field in [Summary] that uses the links coming from [Inventory] pulls data from the {Profit} field, and adds it all using the SUM(values) aggregation function. That would give you summaries by group.

Does that make things more clear?

[Nico_Stanculesc]

With the risk of embarrassing myself, I’ll say that I’m completely lost. But first, Justin, many thanks. I am grateful for your time.

Allow me to detail this - in the attached image, I have 5 types of individuals and I need to create reimbursements for air, ground, meals and hotel. I have created a formula to add these reimbursements for each name.

What I need is to compile a summary, like you suggest, by type, showing the total reimbursement needed for each type AND for each category (air, ground, meals, hotel).

But how on earth can I do that…? What should I have in the primary field? What would be the next step?

I’ve been reading up on the help but I simply cannot find an appropriate example which I could employ.

Any other suggestion is immensely appreciated.

[Justin_Barrett]

You’re actually not far off from what I suggested above. Your {Type} field in that [Speakers & attendees] table is a single select field. What I suggest doing is changing it to a link field, pointing to another table. Airtable should let you build a new table if you don’t have one already set up for this, and it will populate that new table with records based on your current single select options; i.e. the primary field contents will match your current list of choices for the single select. I’m going to call this table [Types].

Next you’ll build a series of rollup fields in that new table, one for each category. A rollup field has three options: the links to follow (this will be based on the table links you just made to your new [Types] table), the field in the [Speakers & attendees] table for the category that you want to roll up (for example: {Extra meals}), and the calculation to perform on the collected data. It sounds like you just want a summary per category per type, so SUM(values) should work.

Does that help?

[Nico_Stanculesc]

Dear Justin - many thanks for your wonderful patience with me.
So, yes, I have followed up, step-by-step your instructions and managed to pull the data in the “Type” table.

Now, last question if I may, please… (thanks millions for this, once more))… If I need to pull a budget tab where I have to add the “type” sums PLUS some new line items (such as hard costs, for instance)… Do I have to create a table for Hard Costs and then the Budget would then be “built” from the “Type” and “Hard Costs”? Seems rather convoluted, no? (feel free to laugh)

Immensely appreciate your guidance. I was ready to give it all up.

Gratefully -

nico

[Justin_Barrett]

Before we talk too much about the technicalities of the next step you describe, it would help me (and anyone else reading who may also be able to assist) to know more about the big picture. Could you please share more about your specific use case? What’s your end goal with this setup? Is this base designed for a single project, or will you be creating various budgets with this system over time? Knowing that will help us suggest the most appropriate next steps in terms of base design.

[Nico_Stanculesc]

Sure! Mea culpa. So, I have a meeting I am planning. I have…

a) Five types of participants (Investigators, Grantees, Staff, Consultants, etc.)
b) must reimburse them for air travel, ground transportation, meals, hotel accommodations
c) must obtain bios, pictures, presentation titles, abstracts from them (use form?)
d) must derive a schedule or agenda by pulling the above info (presentation titles) into a program
d) have hard costs in conjunction with the event (venue rental, catering, audio/visual, printing, etc.)

I need to put together a table for the overall budget of the event. In this budget, I will have:

i) the hard costs (venue rental, a/v, etc.)
ii) the “type” [reimbursement] costs.
iii) estimated AND actual numbers
iv) variance

Does this contour the project better?

Thank you, again.

[Nico_Stanculesc]

Hi Justin - any other guidance for me?

thank you, once more -

nico

[Justin_Barrett]

Sorry, work has been keeping me busy, and time to focus has been hard to come by. I probably shouldn’t have tried to dive into this right now considering how frazzled I’ve been lately.

If anyone else can jump in, I’d be most grateful, as I really don’t know when I can get to this. Sorry!