Relationship to most recent date

[stefangs]

This may have been asked before around here, but I can't find anything, so advance apologies just in case!

I have a parent table with dates, pointing to a child table with things that occurred on that date. I'd like to compare a field in the child table to the field of another child record, based on the preceding date in the parent table.

Can I create a relationship between these tables that returns only a single match? The only thing I can think of would be another field in the child table with a lookup just for that purpose. Seems not so elegant.

Thanks,

Stefan

[mr_vodka]

Hi please elaborate and use specifics instead of using abstract tables and fields. This way we can help you better.

[Fitch]

It's a little unclear to me as well, but perhaps you could load the ID of the other child record into a global field, and then compare the records using a relationship based on the global.

[stefangs]

Sorry, I'll try to give a better description.<br /><br />I call the main table 'Dates'. Let's say I have three records in 'Dates', one each for January, February, and March. It has a layout with a portal to a table called 'Cash'.<br /><br />'Cash' has a field called 'amount'. This field shows up in the portal.<br /><br />When I look at the portal, I want to enter something in 'amount' and be able to see the amount of the previous record in 'Cash'.<br /><br />So the layout with portal should look something like this:<br /><br /><br />DateField: March<br /><br />amount previous amount<br />---------------------------<br />|500 | |450 |<br />---------------------------<br /><br />500 is what I enter for March, the 450 would be the related amount from the record of February. It's enough (and actually desired) to show *only* the amount from the preceding date, not the entire history.<br /><br />If I look at the record of February, then the previous amount shows the amount of January and so on. Presumably, the record of January would show no previous amount (if that's the first record).<br /><br />Hope this makes sense!<br /><br />Thanks,<br />Stefan<br /><br /><br /><br /><br />

Hmm, my text and the nice ascii graphics were ruined by the board's formatting and I don't know how to fix that, if even possible. Sorry about that.

Stefan

[mr_vodka]

Well you could use GetNthRecord to grab the previous record's ID and then have a relationship from that ID to a TO of Cash to grab the related amount record.

 

That being said, can you post the file? I dont think what you are doing is ideal.

[stefangs]

Hi John,

thanks for your idea!

I haven't used GetNthRecord, so I'll look into that. The only problem I see with that perhaps it expects the serial numbers to be in order. It should be able to cope with records not being entered in any particular order or missing serials (like from deleted records). Perhaps it can link to the date instead. I'll give that a try and come back.

To post the file, I'd have to remove a whole bunch of stuff which might be more trouble than it's worth. Btw... I know just enough about FM to realize what I'm doing is not ideal or even 'filemakerish'. I suppose I'm just trying to do something quick and dirty instead of elaborate and correct (kind of like repeating fields in the old days!).

Thanks again,

Stefan

[Fitch]

GetNthRecord uses the sort order of the relationship (or if used on a field in the current table, the sort order of the found set).

[stefangs]

Hi Tom,

that's great and should do it then. I'll give it a try over the weekend. Thanks all for your help!!

Stefan

[stefangs]

The manual says:

GetNthRecord of a related table returns the Nth record of the related set (relative to the current record), regardless of how the related table (or portal) is sorted.

@ Tom: Does that contradict what you were saying that the record pulled is based on the sort order of the relationship?

Anyway, I give up trying to do a quick and dirty hack, because it may be dirty, but by now it certainly isn't quick anymore ;-)

I'm uploading a file that I have stripped of a bunch of stuff not related to the problem. It would be great if you could take a look and see if it can be done with a relationship or if a script and/or lookups are required. If not, it's not the end of the world!

Thanks much,

Stefan

DatesAndCash.zip

[Fitch]

The wording of "...regardless of how the related table (or portal) is sorted" is a bit misleading. The portal sort does not affect GetNthRecord, I think we're clear there. But "related table" here means: if you go to a layout based on the related table and sort, it won't affect the result of GetNthRecord when you're back in the context of the original (parent) layout.

 

The relationship sort order is not explicitly mentioned on the help page for some reason -- but it most definitely does affect GetNthRecord.

[stefangs]

Ah ok, thanks for clarifying that.

Stefan