Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 06, 2022 12:39 PM
I have a base that currently has a single table which is populated via an integration with Jotform. There may be more than one record per customer and I'd like to create a view which will only show the most recent submission.
I've got "last modified" as a field and I'd like to use that as a filter along with the customer's name.
Is it possible? If not is there a workaround?
Thanks
Solved! Go to Solution.
Dec 08, 2022 12:53 AM
re: I don't think I made it clear in my original post but I would like the most recent record for each customer rather than the most recent record overall. Is it possible?
Hi Judith, think we're having a bit of a miscommunication, sorry!
The base and instructions I provided above are examples of how one would be able to get the most recent record, and you would need to modify it to suit your workflow
For your use case, you'd have a table called "Customer" which each of your Jotform entries was linked to, and you would create the rollup field on the `Date` value from the table that contains your Jotform entries there, does that make sense?
re: is it possible to link all the records in one go, and then, going forward, link them automatically as and when new records are created instead of having to link them individually
Yeap, you'd just copy the values of the column that currently contains your customer name (or other identifier) in the table that contains your Jotform entries, and paste said values into the linked field to the `Customers` table. This would handle all your historical data
You could then create an automation to do this automatically for you moving forward
Dec 07, 2022 03:03 AM
Yeap this is possible, although the way to do it is a bit complicated
I've set up an example here that you can reference that shows you how to get the most recent record out of all the records linked to a single record called "Rollup"
Creation / explanation:
1. Have all the records linked to a single record called `Rollup`
2. In the `Rollup` table, create a rollup field on the `Date` value from `Table 1` with the formula `MAX(values)`
- This will give us the latest date
3. In `Table 1`, create a lookup field to pull over the most recent entry date from the `Rollup` table
4. Use a formula field to check the `Date` value against the most recent date pulled in via step 3
---
For your use case, you'd have a table called "Customer" which each of your Jotform entries was linked to, and you'd use the same logic in the example above to set it up
Dec 07, 2022 08:19 AM
Thanks for this, that makes sense. I have a couple of questions though
1. Have all the records linked to a single record called `Rollup` - is it possible to link all the records in one go, and then, going forward, link them automatically as and when new records are created instead of having to link them individually
2. In the `Rollup` table, create a rollup field on the `Date` value from `Table 1` with the formula `MAX(values)`
- This will give us the latest date - I don't think I made it clear in my original post but I would like the most recent record for each customer rather than the most recent record overall. Is it possible?
Thanks
Dec 08, 2022 12:53 AM
re: I don't think I made it clear in my original post but I would like the most recent record for each customer rather than the most recent record overall. Is it possible?
Hi Judith, think we're having a bit of a miscommunication, sorry!
The base and instructions I provided above are examples of how one would be able to get the most recent record, and you would need to modify it to suit your workflow
For your use case, you'd have a table called "Customer" which each of your Jotform entries was linked to, and you would create the rollup field on the `Date` value from the table that contains your Jotform entries there, does that make sense?
re: is it possible to link all the records in one go, and then, going forward, link them automatically as and when new records are created instead of having to link them individually
Yeap, you'd just copy the values of the column that currently contains your customer name (or other identifier) in the table that contains your Jotform entries, and paste said values into the linked field to the `Customers` table. This would handle all your historical data
You could then create an automation to do this automatically for you moving forward
Dec 08, 2022 01:48 AM
Hi, now I even see that there is an option in the workspace settings to choose to leave the URL of the attachments as they are.