Forcing refresh on related to related

[LaRetta]

Hi everyone,

I have a complex relationship which consists of a portal which relates to another table which uses Count() to another table. As User selects an item in this portal, they decrease the quantity which inserts an ID to the grandchild relationship. There is one instance in which the 'count' in the first portal will not update and that is when User changes their mind and reverses their request, ie, deletes the selection. When they reverse, the item is deleted but the calc in the original portal will not re-adjust the count back to the original.

Neither Refresh Window [ flush cache join results ] nor Commit Record/Request will cause a refresh of that calc. Since I'm scripting it, it was simple to add a Freeze Window, jump to a layout based upon the table occurrence where this unstored calc exists (where the calc is displayed on the layout) and then return to the original layout. Because the calc is displayed, it forces it to refresh without Flush Cache or Commit Record and it is instant - no flash either like Flush Cache Join produces.

But it's gotten me to wondering whether others have discovered different methods to force a difficult refresh in situations such as this. I would like to add your ideas to my bag of tricks. I have only had two places in this solution where I've had to play this game. The other situation refreshed with Flush Cache but this one won't. Displaying the calc seems to work well but I want to be prepared for future needs. If I wasn't scripting it, I would be in deep doo doo.

LaRetta

[comment]

Could we see a demo of the original problem? That is, the situation where Refresh Window [ flush cache join results ] doesn't work? I am having trouble deciphering this:

"As User selects an item in this portal, they decrease the quantity which inserts an ID to the grandchild relationship."

[Søren Dyhr]

Although I too would like to see a template, did I notice that you mention Count( and indeed not Sum( - as it is can't we expect any refershing of data dependant on notions more than a relation away ...I guess this has to be categorized in same sector as iteration max'es found in recursive CF's, because recusive relations as such are posible as well.

We can tunnel values from an endless deepth as long as they're stored values and appropriate relations exists. However must fields have a layout presence if they deliberatly or accidentally are forced to be unstored othervise won't they exhibit any value at all, and all kind of dependency goes down the drains as well.

What I suggest, is to turn this assingment into a multiline key instead, and use:

http://www.filemaker.com/help/FunctionsRef-384.html

...simply because it then recalculate on stored fields perhaps even only a relation away then. If some kind of statistics then have to take place on a found sets, then generate the needed records scripted on demand in an other TOG, where the extra table in between exists.

Some of the reasons to explode such keyfields into real records on demand, are however not in itself justified:

http://network.datatude.net/viewtopic.php?t=128

...without the workaround Ilyse here suggests! The redundancy it represents points at getting rid of them as soon the reporting is done.

Now admitted, is the inserting and deletion of values in such a keyfield, something that demands clear script thinking to prevent double and tripple pilcrows sneeking here and there. Very much to our luck comes this CF of Rays in an allow us to get on with other matters:

http://www.briandunning.com/cf/39

--sd

[LaRetta]

I will post sample tomorrow night. I've replicated the structure but it doesn't work yet. By 'doesn't work' I don't mean the refresh issue on row selection but rather the entire TOG won't work in the sample. I played a fair amount of games to FORCE this TOG to work in my real solution. :

As I view the TOG in sample file, I realize I'm off the deep end. It wasn't obvious in my REAL graph because it is HUGE so this 'simple' TOG didn't appear out of proportion. But while re-creating it, it is obvious. I had considered posting for help on it before but I knew creating sample or even explaining my needs would be difficult and it is. Regardless, I now know without doubt that I need help on it.

I wanted you guys to know that I wasn't ignoring your input and I'm working on it.

LaRetta

[LaRetta]

Alright. Create new record, select a Contact, select Customer Return only (I 'dumbed it down' for the demo and only Customer Return portion works). Then populate the globals by clicking the button. [color:green]gProductID is value list of products from THAT Contact so select the product you wish. I STILL can't get that value list right. If you select Contact Mary, it shows she purchased a Thingy but she didn't! I'm still working on that portion. I am not going into detail about the business request unless you need it for clarification. Graph: The blue is what I've added to make this thing work. The grey is my base structure. When you select a product the bottom portal will prefill with only THOSE products within set timeframe (2 years for Customer Return, 4 years for Pickup Request, etc). For Customer Returns, only ItemType 'Stock' is allowed. For Pickup Request, it is multiline of different ItemTypes. Click a row on portal below to 'select' which LineItem sale you wish to 'Adjust'. Notice the 'Avail To Use' will decrease by that amount when it is 'transferred' to the Adjustment LineItems (in portal to right).

Currently, the trash can is generic. Notice that the 'Avail to Use' in left portal will not increase if you delete an Adjustment Line. Then attach script to that button called Delete/Force. It refreshes.

You will ask me why this isn't in LineItems. I can go into why I have moved this process aside from LineItems if needed but it is very complex. I used to have Credit Memos in LineItems along with product returns (like table within table). But this needs to be static data tracked outside (and before) credit/debit memos are issued (as a Workbook). Customer Returns aren't products that come back! They are our Store's 'customer returns' that we issue credits from. Problem was, a store would call in a Customer Return on 1 Gizmo. We would credit them $12.00 (what they paid for it). Six months later, they would call in Customer Return on 1 Gizmo but upon searching their history, they only EVER bought one! I identified double-dipping and other such things. This Adjustments process will track products sold vs. products we take back OR issue half-credits for (and many other things). Because we may change the Unit Price we will reimburse, even the product price must be static data. And only certain products are deemed 'adjustable.'

This post could have easily been 10 pages long but I'm practicing concise.

UPDATE: The Products Adjustable TO and LineItems Adjustable was required ONLY for the darned value list. Ha ha.

LaRetta

AdjustedProducts.zip

Edited February 6, 2007 by Guest
Added Update and green

[comment]

I 'dumbed it down' for the demo

I wish you had - I can't find my way in the maze. So just as a guess, I have attached the following script to the trash can:

Delete Portal Row

Commit Records

and it seems to refresh like greased lightning.

[LaRetta]

It doesn't in my solution. I had tried that first thing when it didn't refresh. Something isn't the same. As I was re-doing it I decided that, since I had to set a global in Products anyway then I might as well set 3 fields. In my solution two of them were calcs instead. And there may be other changes as well. It might also have to do with the size of my real file? LineItems is 500,000 records; file is 900 MB. I'll change to the globals and see if that speeds it up and allows the refresh.

Thanks for taking a look. I am open to leaner ways of making this work, Michael. I just couldn't see them.

LaRetta

[comment]

Well, I only see what you posted (and even that is a mouthful). But I tend to think that the lack of commit after deleting the portal row was the major culprit here. If you have further complexities in your real file, I believe that following with a Refresh Window [Flush...] should take care of them.

[LaRetta]

As I said originally, I had first tried Commit and then Refresh Window [Flush...] and even both and that's why I posted. After your post, I tried them again. Nada; it will not refresh. I am going to go through it and make sure it is now identical to this test file, since I'm using two more globals in Products. Somewhere, there is a difference and I don't think it's only the globals. But the structure I presented is the same.

As for the structure, it would be much simpler if I didn't need to filter the value list by the Adjustable flag in Products. I can't write that flag to the LineItems because some products are set, ie can't be adjusted or returned and it changes based upon special Agreements (per chain store) and current date.

Thanks for giving it a shot!

LaRetta

Edited February 6, 2007 by Guest

[comment]

OK, so it seems we are back to the point where I asked to see a demo of the original problem?

[Søren Dyhr]

LaRetta, I too have problems with pinpointing the problem area. I know it isn't much help, but I would wreck my brains to avoid globals ...by and large. One single thread I learned an awfull lot from was this:

http://www.fmforums.com/forum/showtopic.php?tid/176396/post/204083/hl//

In short is it all in how a solution is structured relational, this isn't stunning news to you I know ...but we might have to take into consideration, why anywhere else, the use of globals as such, recieves a step-motherly treatment.

http://en.wikipedia.org/wiki/Global_variable

I know ...I here turns straight into Edward De Bono loosely thinking mode, but perhaps we need to get subsets of data in a more OOP'ish way:

http://www.fmcollective.com/2006/12/postdesign_cauc.html

Instead of tagging records attribually into membership to a found set?

--sd

[LaRetta]

Well, guys, the portal would not refresh in my real file but I 1) decided to use another approach so I didn't have to place globals in my Products table and 2) I've discovered corruption in my LineItems table.

Each time I experience corruption, I think it can't get any worse but it does. Anyway, that's probably why it wasn't refreshing; because I began to strip a copy of my solution to post here and realized that (after I stripped some stuff) the refresh began to work again and that's when I found the corruption. I just wanted to thank you both and put this thread to sleep.

LaRetta

[mr_vodka]

:therethere: Poor LaRetta and her corruption issues that she has been having...

[LaRetta]

Hey John, each time I find corruption I go backwards to prior good copies. Each time, I go further back. Currently, I've lost almost a year's worth of work (but not hard-earned experience). At this rate, I'll be working in my version 6 files again by this summer.

LaRetta <--- laughing only because crying is unladylike.

[comment]

On the bright side, you're getting younger every day...

[LaRetta]

:laugh2: Indeed! Now THAT'S about the best thing you can say to a woman!

[mr_vodka]

Nah the best thing that you can say is that she is getting younger and prettier every day. :(

[comment]

Yeah, go ahead and CORRECT her - that's a smart move...

[mr_vodka]

lol. Well, she did say that it was "about the best thing" but not necessarily "The best thing". Hehe. But you are right, probably not that wise to argue with her. :(