Get Most Recent Records

[Ninjason]

Situation:

My father owns a winery. There are storage tanks on the property used for storing wine. Sometimes he stores his own wine there, sometimes he rents the storage space out to other wineries. I am attempting to create a database in order to maintain compliance with the government and to track who's wine is in what tank.

The structure of the database is very similar to an invoicing solution. I have 5 tables: CUSTOMERS, STORAGE, WINES, TANKS and LINES. I have included an attachment of the file for anyone who needs to look it over.

What I need is calculation that gets the most recent tenant so I can display that on the record for the tank. I'm thinking I should grab the serial number from the most recent storage report and display the name on that record, but I'm not sure how to code for that in FMP.

I am very new to FMP. In fact, this is my first solution. Any help you can provide would be great!

Thanks,

Jason

WINE_OPS.fp7.zip

[Fenton]

I don't really know which table has the actual date you want to get. But the function you would use to get the "last" entry for a relationship is:

Last ( relationship::date field )

OR

Sort the relationship itself descending by the date field. Then the last date is just:

relationship::date

[Ninjason]

Hey Fenton,

Thanks for the speedy reply.

First, to clarify:

I need to grab the date from the most recent storage report for whichever tank I'm looking at. I guess that would mean looking from the TANKS table through the relationship to LINES and then to the STORAGE table (which displays storage reports). The calculation field would display in the TANKS table. Here's how it's set up:

I checked out the Last function you mentioned, and I noticed that the parameter seemed to ask for a repeating field "Last ( repeatingField )". I tried to get more info from FMP Help, but it doesn't seem to have that function listed. Can you tell me a bit more about it?

[swf]

Here is a sample database(simpler) that demonstrates how to find the last date. It uses max function to find the most recent date.

recentdate.fp7.zip

[Ninjason]

SWF,

Thanks for the help, the Max function looks like it will give me what I need. I was wondering why you created a second table occurance. It looks like you don't even need the "lineitems" occurance because you related the same field (tankid) in both relationships. Does the relationship between "most recent"and "tanks" enable you to display the most recent record from the portal on the bottom of the layout for tanks?

[Ender]

Here is a sample database(simpler) that demonstrates how to find the last date. It uses max function to find the most recent date.

Max() is simple, but inefficient. With large related record sets, max() is much slower than the techniques Fenton outlined.

[Fenton]

Yes, if Last() gives you what you want, if the "last" entry is the "latest", then it is much much much faster than Max(). The reason that Last() talks about repeating fields is that it first appeared in FileMaker 2, before it was even relational. But it works much the same with a relationship.

[Ninjason]

Okay,

I'm trying to implement the calculation, but I'm unsure how. I've got a calc field named "date_most_recent" in the "TANKS" table that has this:

= Last ( LINES::timestamp_create ).

I entered a few records in TANKS representing a few different tanks with numbers 100-104. I then entered a few records in STORAGE to represent a few different occurances of wine being shipped in to one tank only (tank 100). I came back to the TANKS layout, hoping to see the most recent tank record displayed, but I didn't. Instead the field is blank.

I tried having "date_most_recent" = Last (STORAGE::date_create), but that didn't work either.

I've attached the file again to clarify.

WINE_OPS.fp7.zip

[Fenton]

There are NO records in Lines, so of course there's no Last() anything. You need at least one related record to a Tank. I don't really know what Lines are, but it is required for any relationship from Tanks.

[Ninjason]

I really don't know what lines are either. I wasn't sure what to call the table, I just knew that I needed it to complete the many to many relationship between TANKS and STORAGE. I think LINES means line items.

I may try to creat a portal in the STORAGE table that creates records in the LINES table whenever someone enters a new STORAGE record.

[Fenton]

I do not see any reason why there is a Tanks ID in the Storage table if there is a Lines "join" table, as there could be multiple tanks for one "storage order." The Lines table in that case would have a Storage foreign key and a Tank foreign key.

It's purpose would be to split a single "storage order" into multiple placements, in different tanks, for some period of time; each line having a storage order id (customer is implicit/explicit from storage), a wine id and tank id. That's my guess.

[Ninjason]

I don't think there is a tanks id in the STORAGE table. The STORAGE table references the foreign key from TANKS in the LINES table.

I set it up like this:

-A primary key in each parent table (STORAGE and TANKS)

-Then, a foreign key from each parent in the join table (LINES)

[Fenton]

You're right, there is no "tank id", but there is a "tank number", which would also be useless when there's a join table. It also confuses people like me -]

[Ninjason]

It's cool, I understand. There's a lot of numbering systems to track everything in a complex database. A couple of weeks ago, I wouldn't have even known what questions to ask, so don't feel bad.

[Ninjason]

For those reading this post, I've solved the problem of displaying most recent transactions through script maker.

First, I created a portal in the STORAGE table that displayed the foreign key from CUSTOMERS.

Next I created a script which ran off a button on the STORAGE layout that set fields in the TANKS layout to what ever values were in the portal records. That way the tank displayed whatever customer was currently occupying that tank.

If anyone would like me to clarify, please let me know.

Edited October 25, 2006 by Guest