Problem with a lookup and updating a record

[DanBrill]

I have a numeric field A set by lookup via relationship X. This field A also acts as the left hand side of another relationship, Y. This much works fine. But if the value of A is relooked up via X, relationship Y does not update until I exit the record and go back to it. But if I set A manually, Y updates as soon as I leave field A.

How can I get Y to update when A is reset by the lookup without having to leave the field?

The weird thing is, I have this same situation working in another file and it works fine. I can't figure out what the problem is.

This seems to be my main area of confusion using FM 7. Can anyone explain what things will update right away, and what things require you to leave a record and come back in order to make the record update.

Also, what is the difference between a commit record script step v. leaving the record and coming back to it. I would think that that's what commit would mean, but the behavior isn't the same.

Happy Bastille Day,

Dan

[cellmaker]

Dan -

I'm having a similar kind of problem, which has some relation the question posed in

http://www.fmforums.com/threads/showflat.php/Cat/0/Number/114140/an/0/page/0#114140 .

Anyway, my problem in a nutshell is that I have a self-relational lookup ( based on a calculated date, which itself is based on another self-relationship (A). That sounds more complicated than it is.

Anyway, I have a table with dates and costs, as well as some other identifying info we'll call owner and type.

When a record is created, the type is used to look up a key for that type. The owner comes from a parent file (via portal relationship), as is the date for the record (looked up from parent). All this is fine.

My task is to look up the cost of the item from the last (by date) record that also match type and owner.

I'm doing this via two steps:

Get via relationship all records for type and owner with dates earlier than the new record's date, so:

type = type

owner = owner

date < date

This works fine.

Now I have a calculation to determine the latest date, so:

c_latestdate = max(aboverelationship:date)

This also works fine.

Now I have the amount looked up based on the second relationship:

c_lastdate = date

This also works, but not when I create the record. Everything is valid, and it works on relookup, but not when I create the record.

What I find strange about this is that, even if all the calculations/relationships can't be produced "fast enough," eventually all of the pieces fall into place, which should trigger the lookup no matter when it happens, because the lookup base value has changed. And yet it doesn't.

I'm attaching a part of my TOG that shows these relationships.

Are there strategies to get around this rather than the unpleasantness of writing a script to do the relookup afterwards?

Anybody? Anybody? ... Bueller?

LookupProb.pdf.zip

[cellmaker]

Sorry, all. Braindamage on my part. Forgot to flesh out my second relationship to restrict to the owner and type, as shown in this new file.

It works. I'll just chalk this up as a cautionary tale for myself.

Lookupnowworks.pdf.zip

[DanBrill]

Help! The more I dig into FM7, the worse this seems to get. I've been working on some right-brain layout type stuff so far and so haven't noticed it, but now that I'm getting into more serious calcs and relationships and I'm getting more frustrated.

Anytime I change data that is the left side of a relationship, calculations that are based on the right side of the relationship don't update until I exit the record and come back in.

Is this how this is supposed to work? It sure mucks things up. The only way I've gotten around this is to have the data on the left hand side be an auto-enter calc that triggers a "refresh" script using the example plugin.

Am I missing something?

[Fenton]

I'm not quite sure what you mean by "don't update." Do you mean "don't show updated data in the portal"? That may be all that it is, the data is updated, it just doesn't refresh in the portal. That's been a problem with calculations in portals for some time. With 7 you have more possibilities of data in portals, so it seems worse; but I think it's pretty much the same.

Sorry, I'm kind of vague on exactly what's going on. Could you whip up a small example file that shows what you mean? I haven't looked at cellmaker's yet. I will now.

BTW, that seems a good use of the example plug-in, to commit or refresh portals. I wish there was a "commit after" checkbox when you assign a script to a button (different topic, but similar; not really a problem, but it would be an enhancement).

[DanBrill]

Actually I don't even mean in a portal -- just a related field on a layout.

Here's a quick example. Requires the example plug in. 'Message' doesn't update right away unless the script is on. Is there a better way?

Thanks,

Dan

RefreshData.zip

[Fenton]

OK, I must admit, I'm not entirely up on the "separation model." It appears that Calc has a matching record ID for each Organization? So, if you wanted calculations for People, you'd need another file for that, for the IDs?

Despite the above, I got it to refresh. I've found that a direct line between the files works better; which means just another TO of the relevant tables (both in this case; 1 to evaluate the calculation, the other to display the message; I think -).

RefreshData2.zip

[DanBrill]

Yeah, I think that's it. The separation model is not making my life any easier. If the calc is right in the data table and not split out into the calc table it refreshes just fine. Or it can work via your method. But it means setting up more TO's.

I'm coming to believe that if a person wants to use the separation model then it is best to split the data from the layouts, and have the calc table in place when the solution is distributed, but virtually empty. The calc tables are best used 'in a pinch' when you need to add calcs (or even raw data fields for that matter). But if you know you need a calc at the outset, it is just a lot cleaner to put it in the data table as you would in a traditional model.

So if any other brave soul wants to try the 3 file SM as it is described in the documentation, then go ahead and set up the calc tables and add the scripts required to keep them in sync with the data tables. But then put everything you can think of in the data tables and only fall back on the calc tables if you need to.

Ah well, live and learn.

Thanks, Fenton.

[Fenton]

Well, if it makes you feel any better, the reason I thought of trying the extra TO was because I'd run into similar problems with something I was building, which was not a separation model at all. 7 gives you power to reach far away, but can't always keep the screen in perfect sync with the data.

I think the basic problem was that the data was having to pass back and forth through the People table (not just one way either). It's understandable it couldn't refresh. It may be that those TO's would be all that was needed; just one extra per main table. There will be a lot of TO's for other things before it's all over anyway, so I wouldn't think of them as a big burden (yet -)