Filter lookup field to email specific attachments

[SUWR]

We are using Airtable to track receipts. The current setup is a Receipts tab, where employees can fill out a form to submit their receipts.

The Staff tab, is where the list of staff names are that the staff choose from when filling out the form that goes to the Receipts Tab.

Then I have a 3rd tab for Reconciliation Month that the Finance Manager can apply onto the Receipts tab when they do the final approval of the receipts.

The workflow is that an employee will fill out the form when they have a receipt. Then once a month the finance manager will go in and review confirm which month the receipt applies to so that it will group together.

I am trying to get it setup to do an automation that sends the receipts to the finance manager by staff person by month. I am able to do the email that pulls all the receipts together for that month, but can't figure out how to break it down by person. The staff tab has a lookup of receipts by person, but it is pulling all the receipts. I know I can set a filter on the lookup field to be a specific month, but that would require changing the filter every month. Is there a way to do this more automatically where I can get just the month's receipts for that person. I've been trying to do it via interface and buttons and filters, but still can't seem to get it to do just those receipts. 

[TheTimeSavingCo]

Perhaps you could try:
1. Create an automation that'll run once a month at a time of your choosing
2. Use a "Find Record" step to look for all the relevant records in "Staff"
3. Use a repeating group and use the result of the "Find Record" step from step 2
  - This will cause the automation to run a set of actions per staff member record
4. Use a "Find record" step to look for the receipts from each staff member for that month
5. Email the finance manager with the found records

This'll send one email per person per month with all of the receipts from that month in it, does that make sense?

[SUWR]

Could you expand on what the find record step would look like? The "Staff" tab would have all months in a lookup field so it wouldn't separate out by month. I think I would have to do a find record on the receipts tab? The month time period is not quite calendar month (it's CC billing cycle, so slightly off) so not all receipts from that calendar month will be part of the "month". Does that make sense?  

[TheTimeSavingCo]

Hm I'm not sure what you're asking about the find record step, sorry.  The "Find Record" action from step 2 would be in "Staff" to get all the active staff members, and the "Find Record" action from step 4 would be in "Receipts" to get the receipts from each staff member for that month?  Not sure if that helps!

--

re: The month time period is not quite calendar month (it's CC billing cycle, so slightly off) so not all receipts from that calendar month will be part of the "month". Does that make sense?  

Hmm...what if you made the automation run at the end of the billing cycle and have it look for receipts within the past X days, where X represents the number of days in your billing cycle?

[SUWR]

Thanks for trying to help me. I can't seem to figure it out! My setup right now is that I have a button in an interface trigger an automation which emails the receipts for the month selected on the interface. I can't then figure out how to add in the find record to accurately pull for each staff.  I got it to repeat for each person, but it still pulls all the receipts for that month rather than the individual person. This is what the automation looks like. Not sure if that helps you figure out what I'm missing!

[TheTimeSavingCo]

If you could provide a read-only link to a duplicated copy of your base with some example data I could try to set it up for you if you'd like!

[SUWR]

That would be amazing! https://airtable.com/appm5vq4dOChmVX3N/shrFqu1Vy9cg1Im0V

[TheTimeSavingCo]

Ah, that link doesn't give me the ability to duplicate the base and create the automations you need.  Here's Airtable's guide on how to create a read-only link: https://support.airtable.com/docs/adding-a-base-collaborator#adding-a-base-collaborator-in-airtable

[SUWR]

Try this link, I think it should work: https://airtable.com/appm5vq4dOChmVX3N/shrFqu1Vy9cg1Im0V

[TheTimeSavingCo]

Hmm I've put together an automation here for you to check out that I think does what you need?

The link in your latest message is the same as the previous one and when copied doesn't include any automations I'm afraid, and without seeing how you were trying to set it up I guessed at what you were trying to do instead.  Do let me know if the outcome isn't what you expect and I'll see what I can do to help!