Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum

Recommended Posts

Feirefiz

I'm working on creating a portal or a set of fields that will show the last modified record, i.e. current record - 1 if the current record has not been committed. I've looked at this and this post. My attempt to apply the method outlined in the first post results in the current record being shown. That makes sense since the record gets a modification timestamp upon creation (as well as upon being committed?). So I figure if I modified the method to sort by record id (created upon record committal), the Show-Last-Record set of fields should show the last committed record, right? But they show the current record.

 

For the second post, I don't understand where the "Last" comes from since I only see it referring to repeating records. In any case, my attempt to apply that method show the record with the first record id.

Share this post


Link to post
Share on other sites
eos
For the second post, I don't understand where the "Last" comes from since I only see it referring to repeating records..

 

Last() also returns the last related record of a relationship, as per that relationship's sort order. On the other hand, this is not too helpful here since it pertains only to a field, so you needed to get an ID (from the “last” record) and use that for another relationship.

 

Try a relationship where ThisTO::primaryID ≠ OtherTO::primaryID, sorted descending by modification TS; since you only want to display a single record, you wouldn't need a portal.

Share this post


Link to post
Share on other sites
Feirefiz

This also displays the first record, no matter what is created or modified. Think think think… The relationship with sort order look as you suggest. Where else might I have gone wrong?

Share this post


Link to post
Share on other sites
Feirefiz

I think it's the relationship with sort. Entering ever record except the first shows record 1 in the Show-Last-Record fields. Entering the first record shows record 2 in the Show-Last-Record fields.

Share this post


Link to post
Share on other sites
eos
The relationship with sort order look as you suggest

 

Then what is the problem?

 

Think think think…

 

While you're thinking, why not (again) think it through logically:

 

You want to find …

 

• … from all records of this table …: create a self join

• … the most recently modified record …: sort related records by modification timestamp, descending, grab first record

• … ignoring the current record: define relationship as thisTO::id ≠ selfjoin_TO::id; or – to also consider only records that were modified before the current record – thisTO::timeStamp > selfjoin_TO::timeStamp

 

The usual question applies: what do you need this for anyway?

Share this post


Link to post
Share on other sites
Feirefiz
Then what is the problem?

 

The problem with problems is that you don't always know. The result is wrong, the steps look right as I examine and reexamine each one, I think I'm following the logic. Self-join and descending sort by modification timestamp are clear. Maybe I'm stuck at "grab first record".

 

What for: It's a simple bookkeeping solution. This table "Journal" has individual entries in list view, sorted by receipt date. In the header are fields for the current record, plus (here's what I'm working on) the last modified record, so that my colleague (the user) can see at a glance the last entry she made without scrolling down the list.

Share this post


Link to post
Share on other sites
eos
Self-join and descending sort by modification timestamp are clear. Maybe I'm stuck at "grab first record".

 

That just means that via any relationship, a regular reference to a related field targets the first related record; which means that fields from the target TO that you place on the source layout (even without a portal) will display data from the first record, which – if the relationship works as expected – will be the most recently modified one.

 

In the header are fields for the current record".

 

But good you mention the header; the data there come from the current record, and a relationship is evaluated from its context; so “most recent” defined as “older” would mean different things to different records.

 

Try this: create an additional cartesian self-join (id x id), say, Journal_selfAll, create a calculation field as Max ( Journal_selfAll::modTS ), then redefine the other relationship as Journal::calculatedMaxTS = Journal_mostRecent::modTS (no need for a sort). If you never want to show the current record as the most recently modified (even if, strictly speaking, it is), again, define the self-join as id ≠ id.

 

Since the calc field will hold the same value in every record, you get the same related set (actually, it should be only one record), namely, the most recently modified record.

 

Well, that should give you something to experiment with.

Share this post


Link to post
Share on other sites
eos

Well, feel free to give feedback …

Share this post


Link to post
Share on other sites
Feirefiz

Thanks, eos, for the ideas. I'm not that far yet; I couldn't get it to work right away, so I thought I'd do something easy in the meantime (add that sub-summary part) that turned out to take more time than expected. Will be working on it more this week.

Share this post


Link to post
Share on other sites
Feirefiz

I still can't get any record to show via the new relationship(s). I've tried them two ways:

 

Journal::id >--- x ---

Journal::lastRecord >--- = ---

 

 

Journal::id >--- x ---

 

The field "lastRecord" corresponds to your "calculatedMaxTS", and the table "LastModifiedRecord" to your "Journal_mostRecent".

 

I've tried setting a field to show from the above three table occurences, one after another.

Share this post


Link to post
Share on other sites
Feirefiz

Probably I did something wrong. Comparing. Can you really not have Manage Database open for two files at the same time?

 

Thanks for the sample!

Share this post


Link to post
Share on other sites
eos
Can you really not have Manage Database open for two files at the same time?

 

No, but you can open two different versions copies of FM (if you have them) and switch back and fro, or take a screenshot of one RG, open it in TextEdit and compare with the live RG.

 

btw, I realized that you're doing this in FM8, which has no script triggers … I suggest you get this running in FM12/13, then think about a refresh mechanism, which AFAICT is necessary to update the display of the related values.

 

I haven't tried putting the fields into a one-row portal – maybe that would work better re refresh (don't know why it would, but worth a try anyway).

Share this post


Link to post
Share on other sites
Feirefiz

Yeah, I've been doing it with screen shots, but that's tricky too.

 

Luckily the decision has been made for everyone who has FM 8 to trade in their computers & get FileMaker 13, so as of yesterday, I started working on this solution in 12 again. In my solution as it stood at that point, it didn't make a difference. Even adding a refresh step didn't help. Yours I only looked at in 12. I've got both a portal and naked fields on the layout at this point as I'm working on the problem.

Share this post


Link to post
Share on other sites
eos
Luckily the decision has been made for everyone who has FM 8 to trade in their computers & get FileMaker 13.

 

Good for you :D

 

In my solution as it stood at that point, it didn't make a difference.

 

Why not post that file of yours?

Share this post


Link to post
Share on other sites
Feirefiz

Because I might turn purple when you laugh at it! Ok… I tried, but the forum thingy says it's an invalid file.

Share this post


Link to post
Share on other sites
eos
the forum thingy says it's an invalid file.

 

I thought you're, like, a moderator in this forum thingy ;) and know the ins & outs … anyway, try uploading a zip of the file.

Share this post


Link to post
Share on other sites
eos

1. Just naming a field modificationTimestamp doesn't cut it; you need to select the auto-enter option for modificationTS (analogous to creationTimestamp, where you did select the appropriate option)

 

2. When creating a calculation field, always check the data type; you didn't change the default of Number for 'lastRecord'; it needs to be Timestamp, because matching 63557456341 to 21/01/2015 16:58:45 will never find a related record …

 

(and IMO its a good idea to use some sort of nomenclature that distinguishes fields by their type/purpose/something; I use prefixes s for Summary, c for Calculation, zz_ for Metadata, and don't bother with non-derived types …)

 

3. Make sure to add the trigger to all “enterable” fields.

 

4. Add a Commit Record as first line to the Refresh script.

Share this post


Link to post
Share on other sites
Feirefiz

Wow, eos, I've only read as far as 1. You're a hero. I knew it had to be something missed like that.

 

Edited for more:

 

2. I wouldn't have caught that either.

 

3. Yeah, I had just started adding the triggers as a test.

 

Anyway, thank you for taking the time to take a look at the file and finding all the important mistakes!

Edited by Feirefiz

Share this post


Link to post
Share on other sites
Feirefiz

It does indeed work without the script trigger and in this case would probably be better so. If someone just modified record 11 and is working on record 12, I don't want it showing record 12 as last modified till he's left the record. (But if he then goes to record 11 without modifying 11 then back to 12, then I want record 12 to show as last modified.)

 

Ok, edited to add: The portal definitely shows the right one without the script triggers. The field doesn't, but that may be due to some other mistake I haven't discovered yet too

 

edit: like being set to the wrong field. :D

Edited by Feirefiz

Share this post


Link to post
Share on other sites
eos
The portal definitely shows the right one without the script triggers

 

When using a portal, it seems to work OK, even without a script trigger; from what I've observed, when using bare fields, without a trigger it just works “after a fashion”; only the field that you have actually modified is being displayed with the new value, while the other fields show values from the previous last record.

 

Good you finally got it working; and you see, once you know the technique (and some common pitfalls – you'll never again forget to check the a calculation's return type, right? :D), you're much more comfortable experimenting … but I guess that's true for all human activities.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use