A better way of rolling up data between tables?

[travelnsam]

Hi there,

There is probably a solution I am not seeing here because I can no longer see the forest for the trees so to speak - if anyone has any advise it would be most appreciated! I have scoured support/community but cannot seem to figure it out.

I have one table where the primary field may have duplicate entries for the same entity. For example, assume we have Table A:

Mon - 13 units
Tue - 25 units
Wed - 5 units
…etc
Mon - 54 units
Tue - 18 units
…etc etc

From the above, over a period of time each day of the week will have more and more entries. So how best to add up all the units for each day i.e. what is the total for units for ‘Monday’ for all time?

My understanding is that, on another table (Table B), I would need to link a single record to each ‘Monday’ record on Table A, then create a rollup field and use the SUM() function on the Units field from Table A. But this involves having to manually add in the link for each Monday with each new week. And if I already have a list that has a lot of data I have to go through and manually link them one by one.

Is there any way of automatically linking ALL records from Table A with a matching primary field? I.e a link to ‘Monday’ in Table B that includes all records with Monday as their primary field, thus adding new entries automatically?

I do not think this is possible, but perhaps there is some change I can make with the way I am structuring my data that I could change and take a different approach? It may be obvious to someone out there, as I have developed a block!

Many thanks for your help in advance!

Sam

[JonathanBowen · Accepted solution]

travelnsam:

Is there any way of automatically linking ALL records from Table A with a matching primary field? I.e a link to ‘Monday’ in Table B that includes all records with Monday as their primary field, thus adding new entries automatically?

Hi @travelnsam - no, there isn’t a way to automatically link the detail to the summary tables, but there is an easier way than linking record by record.

Set up your summary table with the things that you want to summarise by (i.e. days of the week):

Then in your “data” table create a linked field from the data to the summary tables:

Now, just copy and paste the Day column into the Link column (not record by record, but by selecting multiple records or even the whole column):

Back in the summary table you can add a rollup for the linked values:

You could also write a script using the new Script block that achieves the same with a single button click, but copy and paste is pretty quick for this type of task

JB

[Mohamed_Swella1]

Hi Sam,

travelnsam:

My understanding is that, on another table (Table B), I would need to link a single record to each ‘Monday’ record on Table A, then create a rollup field and use the SUM() function on the Units field from Table A

Yes, that is correct.

travelnsam:

But this involves having to manually add in the link for each Monday with each new week. And if I already have a list that has a lot of data I have to go through and manually link them one by one.

No, you can workaround it in several ways.

First Option (no script block): Click on the field, choose Duplicate Field, Duplicate Cells. This will create the same field again with the same values (field name will be the same + “Copy”). In this duplicate Field, choose “Link to another record, create new table”, this will create a new table that is already linked to this field and you can perform whatever Rollup functions you want.

Disadvantage would be that every new record you create you will have to manually add this field. What I did in a similar situation was add a Color where if this field is empty while the first field is not empty there would be a red color beside the record so I would remember to do it.

Second Option: Same as first option, but instead of having to add the Linked Field manually every time you add a new record, use the Script Block to do that for you. I dont have experience in this part but maybe someone can help on that. Maybe this Post would help.

BR,
Mo

[travelnsam]

Hi Jonathan. Many thanks for your response, and clear explanation (and diagrams!). I had used copy/paste before, but I never thought to do it from the source table! I always added the links from the target table i.e. adding in multiple links to all the ‘Mondays’ one by one in your Summary table - which (a.) gets onerous if the source has lots of records and (b.) is prone to human error, missing a record or clicking on the wrong one. This approach is MUCH better. Thank you!

As per my response to Mo below, the one issue this does not solve is when new records are added. But perhaps the only way to cater to this is using the Scripting Block (which I am loving). I will decide if the ‘new record’ issue is worth his effort, or your new/improved copy/paste approach is good enough for me.

Will mark this as the answer, as it is a big step towards my goal. Many thanks for taking the time to respond, JB.

Sam

[travelnsam]

Thanks Mohamed - you hit the nail on the head where the issue is adding records later . I was not clear in my question. I was aware of the copy/paste approach, but whenever I add records later that immediately falls out of date. But marking ‘new’ records with a color is a good idea as a reminder - thanks! I have used the Scripting Block and indeed could do something with that, I am sure. I just wanted to make sure I wasn’t overlooking anything obvious and blindly running along reinventing the wheel!

Many thanks for your ideas - most appreciated.

Sam

[JonathanBowen]

travelnsam:

the one issue this does not solve is when new records are added

Hi @travelnsam - not sure if I understand you correctly here, but perhaps you mean the following:

Let’s say you are recording data for Mon, Tue, Wed each week and have multiple records in the detail table and 3 records in the summary table (with a rollup, as above). Now you decide to record Thursday too, so you have a new record in the detail table for the first week. Again, just copy and paste as above and Thursday will be created in the summary table automatically (with its matching rollup value). Once the link between the two tables is made, copying and pasting data in the source/detail table means you don’t really need to do anything else in the summary table.

JB

[JonathanBowen]

Or if you just mean that you have to copy and paste every week for the new week’s records, then, yes, I’m afraid that’s true.

[travelnsam]

Hi JB. Yes the latter scenario - as new days/weeks are added, I have to go in and manually copy/paste again.