Archive by month Query


I currently have a table where each row contains an item that has a button to send this information to a particular supplier. However, what i would like to do is that once this information disappears after the info being sent, i would like it to move/duplicate into an archive table organised by month so I can clearly see how many rows ‘items’ i’m sending each month from said table to said supplier.

What would be the simplest way to do this?

Thanks in advance!

Hi Elisa,

From what I understand, I see a couple different approaches, both involving a new table that has a Lookup column to the source table.

In one approach, you could simply add a new entry to the new table each time the button is clicked, with a date. Then you could do reports/summaries on that data however you want - weekly, daily, monthly, yearly. The downside could be if you have lots and lots of entries (thousands, tens of thousands), that you might run into performance problems. However this is a good flexible approach.

The other approach would be that when the button is clicked, check if the new table has a row for the current month for that source item, if not, add it. Then increment a number in a “Monthly Sends”.

I would aim for the first approach, since it cleanly records all sends as individual dated items and you can do with it what you want. If the table gets too long after a long period of time, you could consider processes to archive old data in other tables or other documents (using cross-doc).

1 Like

Hi Elisa,

I like @Ed_Liveikis’ first proposal, but would go one step further - why have a separate table at all? Record the date when the button is pressed, and then filter on that column.


@Piet_Strydom it seems to me that wouldn’t be sufficient based on:

clearly see how many rows ‘items’ i’m sending each month from said table to said supplier

…as we need to know how many per month, not just that last send time. Also it’s not clear what the sending rows are like, if you can change the supplier in a dropdown, etc. It’s hard to say without more info.

Thank you everyone for your responses.

There is a dropdown for multiple suppliers and as each new order comes in the table it creates a new row per item and then each row gets sent to a supplier we choose.

Once the row is ‘sent’ the row currently disappears from the table but i would like a record per month of the rows for each supplier that has been sent so I can clearly see for example how many of X were sent to supplier A or B in each month.

Oh alright then maybe what Piet mentioned is better. So instead of deleting the row, you could add an integer column _sent, that gets set to 1 whenever it is sent, and just filter the table to not show sent rows. Then you could have a button or automation to populate another table with the summary info about how many were sent to each supplier each month.

You only really need to copy/move the sent items out to another table if you run into performance problems.