Jump to content
copies

Lookups perform from memory cache, not from current found set

Recommended Posts

copies

I'm using FM Developer 7. I have two files: (File 1) contains input records and a summary layout. (File 2) is a duplicate of the summary layout with lookups to the fields on the summary layout of (File 1). I'm doing this so I can delete the source records in (File 1). The relationship is triggered by the date field in the two files:

 

"When a new entry is made in the field "Date", this lookup will copy the value from the first matching related record in the table "File 1".

 

The lookups work fine initially, but if I add new records in (File 1) which change the summary layout, the lookups in (File 2) will not update. In fact, if I create new records with a new date, and perform the lookup, I still get the original lookups but not the new information. I can even delete the source records in (File 1), and their information will still populate the lookup fields in (File 2)!

 

If I completely close Filemaker, then re-open it, the lookups work (but only until I add a new record).

 

Can anyone tell me What's going on?

Share this post


Link to post
Share on other sites
Ender

I haven't seen this behavior. Can you whip up a simple demo illustrating the problem?

Share this post


Link to post
Share on other sites
copies

Ender, thanks for taking a look at this. I'm stumped!

Share this post


Link to post
Share on other sites
Ender

Since summary fields are based on the found set of the file & table they reside in, they cannot be used as the source for a lookup.

 

Instead try filtering the relationship by whatever criteria you wish, then use an auto-enter calc with a sum() function to pull the aggregate data over.

Share this post


Link to post
Share on other sites
comment

I see a bug here. But I agree with Ender that this is not a good way to go about this, bug or no bug. If you omit some records in File 1, the summaries will change to reflect that, but File 2 always sees a summary of ALL RELATED records.

 

If you need to freeze the summary of a FOUND set in another table/file, you will need to script the data transfer.

Share this post


Link to post
Share on other sites
copies

Thanks, Ender and Comment. I really do need to freeze the summaries--and without being able to use lookups I know of no other way to do this than copy the results to File2 with a script. Problem is, in the actual database there are quite a few summary fields that would have to be scripted, and this would slow down the operation noticeably.

 

Any other suggestions? If the lookup function is buggy, do you know if Version 8 works? And if it does, why wouldn't this be a good way to go about it?

Share this post


Link to post
Share on other sites
comment

It is not a good way, because refering to related summary fields is currently an undocumented feature. It works reasonably well in some contexts, less well in others. I have tested your file in v.8.0v1, and it's the same there.

 

Unless there are other considerations you haven't told us about, I'd join Ender's recommendation to use auto-entered calculations of:

Sum ( File1::AmountX )

 

To make it behave more like a lookup, you can de-select the "Do not replace..." option, and change the calcs to:

Let ( trigger = Date ; Sum ( File1::AmountX ) )

Share this post


Link to post
Share on other sites
copies

Comment & Ender, thanks again for your help, and for taking the time to look at the file I uploaded.

 

I did indeed give up on the lookup, and have used the auto-entered calculations. The seem to be doing the job OK.

 

But I can't figure out what the "do not replace . . ." option does. I've tried different things with it, and a field doesn't seem to react any differently with it checked or unchecked. I'm concerned that something may pop up in the future because I left the box unchecked. Any suggestions?

Share this post


Link to post
Share on other sites
comment

When the box is unchecked, the calc will update when you change the date - IF the date is referenced in the calc.

Share this post


Link to post
Share on other sites
copies

Thanks, Comment. That was the one thing I didn't try. I think I can finish the project now, thanks to you and Ender!

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