Show latest records only

Ask the Community Get Answers
1

Hi all,

I think this is doable but for the life of me I cant work out the best way to do this.

I have a table that contains stocktake information per site, per week.

I would like to have a view where I can see all the sites, but only show the latest entry for them. For example some sites only submit once or twice a year, but some need to submit each week. Ideally the report would just show the latest entry but for all sites.

At first I thought I could filter it in the grid, but I cant quite get that to work, my second thought was a task the update a field to show its the latest one, but then I would need to work out how to unmark the older one also.

I think I have confused myself with it to be honest, so any help is appreciated.

Chris

2

1st sort by the newest record.
2nd on how many records you want to show enter 1 only.

may be this help.

3

Limiting to just one record using a sort/filter in a grid view would only return one record total, not one per site. :thinking:

4

Haven’t really tested this idea .. just a thought bubble.

Connect the stocktake table to itself, have a “most recent” flag in the table, default is yes. On create, use record rules to update all connected records with same site to most recent = no. Show a grid of all sites set to yes .. should only be most recent one per site.

1 Like
5

that’s an interesting approach. i will see if i can test it and feedback. i do wonder if the record rules will try to update itself in some way.

6

I think this would work if i was just looking at one of the sites, then yes, this will give me that sites last stocktake. But i would like to have 1 list of all sites.

7

It should work for all sites assuming they are all in the same stocktake table.

8

I take it back - this approach wont work. Without having played with it, I was thinking that you could filter the “update connected records” based on a form field, but now that I’ve had some time to try this out it appears that isn’t possible - the filters are absolute values only, not variables. Damn.

9

I feel slightly better now knowing that its not just me that was stumped with this.

I will keep trying and see what can be done.

10

Ok, this annoyed me, so I have a solution for you that at least works in my sandbox.

I assume in your system you have a connection from Stocktakes to Sites - each site has many stocktakes, each stocktake has 1 site.

I assume you have a date/time field in Stocktake.

Add a date/time field to SITE, call it LATEST STOCKTAKE TIME.

When adding a STOCKTAKE, update connected record SITE as below:

image
image1197Ă—461 32.3 KB

Now you have a reference to the latest stocktake time for that site.

In STOCKTAKE table, create an EQUATION field called LATEST or whatever you want, as a filter. It should be like this:

image
image570Ă—266 11.9 KB

Now each stocktake will have a time comparison between it and the latest stocktake for that site. And assuming you don’t do more than one stocktake per minute, only one stocktake per site (the latest one) will show a result of 0 in that equation.

Then you just create your grid to display only those stocktake records where LATEST = 0, sorted however you want, and there you have it…

1 Like