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.
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?
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?
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?
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?
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!
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!
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!
That would be amazing! https://airtable.com/appm5vq4dOChmVX3N/shrFqu1Vy9cg1Im0V
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
Try this link, I think it should work: https://airtable.com/appm5vq4dOChmVX3N/shrFqu1Vy9cg1Im0V
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!
So weird that you couldn't see it, but thanks for giving it a try. You for sure got part of it! Thank you so much! I did now get an email for each staff person. Two follow ups - it still sends me all the receipts rather than just the month I have chosen on the interface. What step am I missing there? And then is there a way for it to not email if there are no attachments?
I think I got it now - I had picked the wrong attachment option, so I think it is just doing the month now! Thank you so much for your help with this!!
Hi - thanks so much for your help with this. One new question related. Our finance manager is interested in being able to email the receipts by person (by selecting the person) in certain scenarios rather than emailing everyone all at once. Do you have a suggestion of how I might set that up?
Hi - thanks so much for your help with this. One new question related. Our finance manager is interested in being able to email the receipts by person (by selecting the person) in certain scenarios rather than emailing everyone all at once. Do you have a suggestion of how I might set that up?
Hmm, maybe use a Record Review layout with the source being the 'Staff' table, and have a button that'll trigger an automation to look for the receipts linked to the selected record and email them?
Thanks - still having trouble there. If it pulls from the staff table it just pulls all the receipts. Using the record review layout on the interface, the only button options are open record creation, go to interface page and go to external URL, it does not give the option to connect to an interface.
Thanks - still having trouble there. If it pulls from the staff table it just pulls all the receipts. Using the record review layout on the interface, the only button options are open record creation, go to interface page and go to external URL, it does not give the option to connect to an interface.
re: If it pulls from the staff table it just pulls all the receipts
Hmm, how are you setting up the automation to find the receipts for the relevant month?
---
re: Using the record review layout on the interface, the only button options are open record creation, go to interface page and go to external URL, it does not give the option to connect to an interface
Could you provide a screenshot of the "Action" dropdown in your Interface? I have the "Run automation" option available here:
For the month pull, I have the button on the interface:
When I choose the month listed on the interface, that's which receipts it emails.
Then, the automation:
For the record review - this is what it looks like for me. The only place I can add a button that triggers an automation is when it's a start from scratch interface page.
For the month pull, I have the button on the interface:
When I choose the month listed on the interface, that's which receipts it emails.
Then, the automation:
For the record review - this is what it looks like for me. The only place I can add a button that triggers an automation is when it's a start from scratch interface page.
Hmm your automation looks like it's set up right and so I can't comment further I'm afraid. I'd need access to the base itself via a read-only link so I can see how your automation's set up specifically to figure out what's wrong, and here's the link to the documentation on how to do that: https://support.airtable.com/docs/adding-a-base-collaborator#adding-a-base-collaborator-in-airtable
For the Interface button issue, try clicking the right panel and adding the button there:
