Find last entry

[Pedroble]

Hi,

I'm very new to FileMaker having been tied to excel for many years

In excel I have lists of Weights of Cattle going back many years which can give many fields

... 11 x 12 in the current example

For Filemaker I have accomodated the data,(rightly or wrongly), in yearly records

My first question is ....

How to make a calculation to find the last entry , ie., "451"

Thanks in advance,

P.

[efen]

I think that I need more information on how your file is structured before I can offer any help (and then the assistance may only be the advice to restructure your file).

Is your file just a copy of an Excel file?

Are the rows the records, each record with the 12 month fields, or is this something different?

I assume that there is more than one animal so that this grid would be repeated for them. Would the last entry be in the same month/year for every animal?

...and welcome to the forums

[Aussie John]

This doesn't answer your question but it seems to me that your database should be based on the particular cow with a weight and date weighed. From the date you could extract the year and date.

If you use a modification date field you could then sort it to find the last modified record.

[Pedroble]

efen,

Thank you for your reply.

Yes, I adapted my Excel records which had one row for each animal with a weighing each month.

The Excel spreadsheet starts in 1996 with one column for each month.

About 25 animals are born each year and roughly the same quantity disposed of.

That's 16 years of data ( x 12 months = 192 columns)

Seemingly too much for Filemaker.

So, I divided each animals data into yearly registers of 12 months

and imported to Filemaker giving nearly 2,000 registers.

The example shown is, I believe, a portal

As you say, the rows are the records, each row with the 12 month fields.

(cont ,,,,)

[Pedroble]

I also have the records sorted, (filtered), by year so that the monthly weighings can be easily updated ...

... the last entry referred to in my original question would be April 2012 in the majority of the registers shown above but not

for 'FANTA' whose last entry is in Febuary nor the others who left the list in previous years.

[Pedroble]

Aussie John,

All the animals, new and old, are weighed on the same day of each month.

The growth performance of each animal is studdied individually and also

in groups, some of which may include past animals..

[LaRetta]

Hi Pedroble, welcome to FM Forums!

Unfortunately, you are maintaining the Excel spreadsheet dynamics.

That's 16 years of data ( x 12 months = 192 columns)

Seemingly too much for Filemaker.

FileMaker is not a spreadsheet - it is a database. It handles large amounts of data quite nicely but it must be normalized. Before you carry this much further, I suggest that you stop and ask the experts for free opinion (that they normally charge for) on how you should be structured.

No 'rightly or wrongly' about it ... it is wrong as you have it. Bringing bad news doesn't bring me joy; on contrary. But the good news is that you can still correct It. There are many wonderful developers here willing to help you understand relational theory. :laugh2:

[Ocean West]

Pedroble,

Welcome to the forums,

you have a few things going on and you may want to first try to un-think of how things work in excel.

in a databases as others have mentioned its all about data normalization and how to structure the data

so that you maintain expected results.

I see this so far:

Table 1: Animal - this would contain a record for each animal

Table 2: Weights - this is a record for each animal for each date and it's weight (perhaps a comment)

Table 3: this is a bit trickier as it is strictly for reporting its a cross-tab report.

A cross tab report takes vertical data an pivots it so that you can display it in a manner that makes sense

such as Animal /weight for Jan - Dec for each year - having totals & averages etc.

FMP doesn't natively produce this type of report but it is possible to do so.

The benefit of storing the data Normalized is that it opens you up to produce different reports that sorted or summarized that may also be of meaning and use in your operation. Such as being able to graph or chart the weight for an animal over time.

[LaRetta]

Hi Pedroble,

I have a sneaky suspicion that you were offended by my post (smile). It was certainly not my intent. The thing I see the most in this business is folks new to the database world begin designing when their only prior experience with data is from spreadsheet perspective. And these folks spend (hundreds) of hours of their blood, sweat and tears taking their 'database' to lengths and then suddenly they find themselves in a box canyon and their only option is to start over because unraveling their knitting back almost to the beginning would take even longer. And all of that work put into it was not only wasted effort, but reinforced wrong-way thinking about structure.

My energy in that post was clearly positive and well-meaning - wanting to give you the best wisdom I have accumulated in this business. I stand by what I said but my tone which came across in typed text could have been better worded (as Stephen so elegantly demonstrated). For that alone, I apologize. And I still, as I said in that post, offer my help in working you towards that goal.

And yes, leave the negative 1 there. I am not responding because I wish you to remove it.

EDITED: I realize that last sentence could be taken either way, LOL, and I meant that I responded because I did not want to offend and not because you gave me a negative rating, which is considered really bad on this forum.

Edited April 14, 2012 by LaRetta

[Dr.Gopala krishnam raju AMBATI]

i hate too much for filemaker oh god remove the limits i want filemaker to be beyond limits

[Rick Whitelaw]

Dr. Gopala,

Speaking for myself, I tend to work on my own limitations and eliminate them one by one. While it may be true that FileMaker has limits, my limits are such that FileMaker allows me plenty of room to learn and grow. Filemskers limits are not the oroblem! I've been doing this a while too.

[Dr.Gopala krishnam raju AMBATI]

yep we work according to our necessary limitations but always ready to know new things in filemaker , for a doctor filemaker is more than decades of learning medicine . filemaker is a limitless creativity with some limits i wish i could reach the border of its limits one day :titanic:

[Lee Smith]

The limits are self-inflicted. Learn the product and the limits will disappear.

i hate too much for filemaker oh god remove the limits i want filemaker to be beyond limits

The limits are self-inflicted. Learn the product and the limits will disappear.

[Ocean West]

My first question is ....

How to make a calculation to find the last entry , ie., "451"

Back to your original request:

If the weights field is a REPEATING FIELD then

Last ( Weight )

If each field is separate then try:

Last ( List ( Jan ; Feb ; Mar ; Apr ; May ; Jun ; Jul ; Aug ; Sept ; Oct ; Nov ; Dec ) )

[Pedroble]

Ocean West,

Thank you for the guidelines - I have set up a mini file with Tables 1 & 2 and am now experimenting with the next

Table 3: this is a bit trickier as it is strictly for reporting its a cross-tab report.

A cross tab report takes vertical data an pivots it so that you can display it in a manner that makes sense

such as Animal /weight for Jan - Dec for each year - having totals & averages etc.

... but have not found any reference to 'cross-tab reports' in FMP

[Vaughan]

Google "filemaker cross tab".

[Pedroble]

Thank you everyboby for your help.

The internet is not always available,

but I shall be back when I have had

more time to check out 'cross-tabs'

and that Last(x) function.