how to compare previous & current record and return result

[direwolf]

Hi all,

I'm brand spanking new (here and largely to Filemaker). Just loaded FM8.5 and am trying to get my head around GetNthRecord. At least I think that's what I need to use for this.

I would like to compare the previous record in a field to the current record. If the two don't match, I want to put ### in another field.

For example. Say I have a db of 500 records, and a field called Endorse has 100 records with data of 56001 in it and then 150 records with 43255, then 200 records with 68324, and the final 50 records with 93872.

I'm trying to figure out how to insert ### in another field called Break, but only for the first occurrence of each of the above in the Endorse field. So, I'd wind up with ### for 4 records in the Break field.

I think I need the GetNthRecord for this? And need to compare recordnumber -1 to the current record and return ### if the two don't match?

Thanks for any help!

[mr_vodka]

Try something like

Case ( field ≠ GetNthRecord ( field; Get ( RecordNumber ) - 1 ); field )

[Søren Dyhr]

John, have you seen the number of records, this is indeed cascaded or chained dependency of UNSTORED calc'fields. This is out of the database realm ...way into a spreadsheet'ish dito.

To prevent the solution from turning slow as molasses, at least use the lookup last function to make the value stored.

Since the word "Break" is involved would it probably be better to teach the virtues of summary reports!

--sd

[mr_vodka]

True Soren, the preferred method would be a summary report, but I didnt think that 800 records was that large.

[direwolf]

Cool. You guys are certainly fast and helpful.

In messing around with it, I found that an If actually works great:

If ( Endorse ≠ GetNthRecord ( Endorse; Get ( RecordNumber ) - 1 ); "###"; "" )

(Even on thousands of records).

Is it possible to skip record 1 when applying the above? If I can do that this will be perfect!

thanks again!

[Genx]

Hi Direwolf,

If ( Get(RecordNumber) > 1 and Endorse <> GetNthRecord( Endorse ; Get(RecordNumber) - 1 ) ; "###" )

Or you could also just embed it in another If statement:

If( Get(RecordNumber) > 1 ;

If( Endorse <> GetNthRecord( Endorse ; Get(RecordNumber) - 1 ) ; "###")

)

Hope that helps :o

Edited June 13, 2007 by Guest

[direwolf]

That's great. Thanks again for such quick and helpful replies.

[Søren Dyhr]

In messing around with it, I found that an If actually works great:

If ( Endorse ≠ GetNthRecord ( Endorse; Get ( RecordNumber ) - 1 ); "###"; "" )

(Even on thousands of records).

Give it a stab in a networked solution then, a layout showing at least 50 records in listmode, send via a vpn tunnel ...to you labtob connecting via a gsm modem, way out in the contryside where hotspots are scarse.

--sd

[Genx]

... It'd take like 10 minutes to load the google website on those settings LOL!!!!

[MrCedars]

Hi,

I am using FM 8.5 Advanced.

Sample Layout results, what i am trying to do is compare the Start and Finish records after the first line

Start-------Add-------Result

1...........1............2

4...........2............6

6..........-1............5

I have a cross-tab layout, so when I go to Preview I have this section

as Sub-summary when sorted by DATE.

The fields are all Calcs, what I did do to accomplish this was use the

" GetNthRecord ( fieldName ; recordNumber ) ".

So I have " GetNthRecord(Result;Get(Record Number)-1) ". The

problem is that this works fine on the Parent related table, but as

soon as I take that field and put it on my layout, all I get is

blanks.

Big Thanks

[Genx]

Hi MrCedars and Welcome to the forums

I'd love to help, but i've not a clue what you're on about... "Start and Finish Records after the first line"?

What exactly is a start record, what exactly is a finish record, and what exactly is a line?

[MrCedars]

To clarify some more, we use to have an excel sheet where we keep track of daily sales and the aging line.

So in excel we would have a sheet, it would total all the days sales add it to yesterdays aging line and would equal todays aging line, if not it would give the difference.

So as the sample above:

Start-------Add-------Result

1...........1............2

4...........2............6

6..........-1............5

The 1 is the beging aging line

The 1 would be that days sales

The 2 would be starting aging line plus sales

The 4 would be a formula of =c1

The 2 would be that days sales

The 6 would be yesterdays aging line plus sales

And so on...

So all my fields are Calculations they all work fine in a Cross-Tab report, the problem I am having is pulling the quantity of yesterdays aging line and adding it to todays sales and then comparing it.

The aging line resides in a parent table that is related.

It pulls over fine, I tried defining the GetNthRecord on the Aging Line table where it displyas the previous record, it works fine but when I put in on the layout of the crosstab i don't get anything but blanks.

I am sorting by Date, both of the tables are sorted by date.

Thanks

[MrCedars]

Hi MrCedars and Welcome to the forums

I'd love to help, but i've not a clue what you're on about... "Start and Finish Records after the first line"?

What exactly is a start record, what exactly is a finish record, and what exactly is a line?

Thanks and sorry I am not the best at explaining things clearly, so if you look at the following table i made in excel, I am trying to mimic the same thing in filemaker:

-----A---------------B--------C

1____AGING LINE......SALES...NEW AGING

2____1...............1.......2

3____2...............1.......3

4____3..............-2.......1

A2 -> Is the starting value

B2 -> Is the sales for that day

C2 -> IS the starting aging + sales

A3 -> Is C2

B3 -> Is the salses for the day

C3 -> Is the A2+B2

A4 -> Is C3

B4 -> Is the sales for that day

C4 -> Is the A4+B4

In filemaker i am importing these values via odbc there are hundreds of sales, then summarizing by date and placing the Aging Line from a related table by that days sales, which all works fine, the problem i have is taking yesterdays aging line and placing on todays date, I was using the GetNthRecord and it doesn't work out for me.

I am trying to add yesterdays aging plus todays sales and compare that against the new aging line, the reason this is done is the customers sales reside in one table and the accountr receivable statement in a another so each day they have to balance.

Big Thanks

[Søren Dyhr]

GetNth( should work, but perhaps you could get inspired by this old dusty ledger template:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

Strictly speaking would I prefere the lookups over the GetNth in the autoenter, because you can issue a relookup, if you strain records away from the import, where another field is needed to trig a re-evaluation of an autoenter.

--sd

Edited June 19, 2007 by Guest

[MrCedars]

GetNth( should work, but perhaps you could get inspired by this old dusty ledger template:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

Strictly speaking would I prefere the lookups over the GetNth in the autoenter, because you can issue a relookup, if you strain records away from the import, where another field is needed to trig a re-evaluation of an autoenter.

--sd

Thanks the lookup worked perfect, i still can't get the GetNth to work.

Cheers

[Genx]

I don't know, I'm still a bit confused, and given the reliance on date, I don't think that GetNthRecord is appropriate here given that it relies on a sort order (its should probably be pulling from a < date relationship).

Your examples, i think are good, but my head isn't. Can you post your excel file with an example of what you're trying to do (given that formula's are fairly easy in excel).

Cheers.