FM 11 Portal Filtering

[bruceR]

I think FM 11 portal filtering will have a lot of great uses.

It also presents some problems that will need to be solved.

For instance, you can set up a filter that might show invoices by date or customer or something. Handy. But - how do you get the total for this filtered subset?

The existing sum() function won't work, because it only operates on the basic relationship and is not affected by the filter.

Of course, you could create a let function in the filter calc that creates a global variable. Hm.

[Perren]

My personal guess is the portal filters are to help lighten the load on the UI side, not the data side so much.

I.e., by being able to filter a portal now you don't need to hide a stack of tabs with individual portals pointing to all your "hand crafted" filter table occurrences. Now just one portal instance can be used for a variety of perspectives where that wasn't possible before...

[mr_vodka]

I had asked FMI at DevCon why they didnt consider taking this one step further and allow filtering of relationships which would be extremely powerful and cut down on the number of TOs. I didnt get very far with an answer. lol.

Anyway, I had put this in as a suggestion 2 months ago. Perhaps more people can suggest it as it would be awesome.

When first seeing it I thought that is awesome. Well most of my portals have sums and so when I was testing 11 I saw there was no option to accommodate for the filtering of the portal in the aggregate, I was disappointed.

Oh well perhaps next time. Another feature half implemented. lol.

[bruceR]

I suspect we'll have a workable method soon though.

[bruceR]

And we're done.

Create a single line filtered portal below the main filtered portal. Same filter for both of course. Put summary fields in it. That's all you need.

Now, I bet you could expand on this and create subfilters too. Parts/labor for example.

OK, yup, tried that too. Nice.

Edited March 10, 2010 by Guest

[bruceR]

Oh boy oh boy. GTRR works too; including the sort order of the particular portal.

[Larry Nichols]

When I set a script to use GTRR and "Show only related records" it takes me to the found set for all related records, not just those in the filtered portal. Did you get GTRR to acknowledge the filter somehow?

[bruceR]

The button is IN the portal, right?

[Larry Nichols]

Yes, just attached it to one of the fields. The related set in the portal was 1 record, the full related set was 3 records. GTRR takes me to a list of the 3 records. You get the smaller, filtered set?

[bruceR]

Yes, GTRR goes to filtered set. What exactly does your script do?

At least for test, try NOT using a script; just a GTRR button - ensuring correct context.

[Larry Nichols]

Tried both ways, but script just had a simple GTRR step in it.

I'm just using the FM11 Invoice template for testing. On invoice detail, attached a button to the line items portal. Filtered the portal to only show items with a price ≤ 15.00. One product in list, but script (or simple GTRR attached to button) takes me to a set of all three line item records.

I completely believe that you got it to work, but not sure why I can't. Seems like should be pretty straightforward. I checked all the layout-level script triggers to make sure some script wasn't being fired somewhere -- nothing. Stumped.

[Larry Nichols]

Ah, got it. I was wrong, there was a script being triggered onLayoutExit. All it did was capture window dimensions and layout name into a series of global variables, but apparently that's enough to disrupt the filter. I'll experiment a little more to see if any onLayoutExit messes up the GTRR. Seems like a bug, though. Thanks for the feedback Bruce.

[Larry Nichols]

Yeah. On the Invoice detail layout I attached a script to be triggered onLayoutExit. The script just has a Commit step in it. When a script with a GTRR step is triggered from a filtered portal, that "Commit" script somehow interferes with the GTRR's reading of the filter, and takes you instead to the full related set as defined in the relationship graph. Seems to work fine if there are no scripts triggered by onLayoutExit on the originating layout.

[bruceR]

Of course a commit interferes. You're no longer in the filtered portal; the context is now the complete set of related records.

[Larry Nichols]

Yeah, the point is that any script triggered by onExitLayout interferes with the GTRR, causing it to ignore the filter. By all means give it a try.

[bruceR]

It isn't clear what your point is.

The script does what you tell it to do. If you want to commit and ignore the filtered portal, then do so. The results are known. If you want it to do something else then program something else to happen.

Specifically, if you want it to select a filtered portal and GTRR based on that portal, then write the script to do that.

[Larry Nichols]

Just trying to get a better handle on what might mess up the GTRR on a filtered portal. FM's knowledge base article was vague about it:

Go to Related Record:

This script step can potentially expose all the related records for the portal, not just the filtered records. This is somewhat unavoidable, akin to how a portal with sorting enabled would not go to the new layout with the records sorted the same way as the portal had been.

So my point (again) is that having a script triggered by onExitLayout will cause a script with a GTRR step to ignore the filter if fired from a filtered portal. Seemed like a point worth stating, since the feature is new and unfamiliar, but then I'm not a Pooh-bah.

[bruceR]

But what you're saying simply isn't true.

Write the script to do what you want. See attached.

Post an example file and we'll look at it.There is something you're doing with exit layout that we need to look at, and we need to see what you're doing.

Contact_Management.fp7.zip

Edited March 10, 2010 by Guest

[mr_vodka]

The Exit Layout script step does fire when the layout is switched by the GTRR step and although the GTRR step is earlier in the script stack, the trigger places the called triggered script ontop of the stack. So by the time that the GTRR is done it is not even coming from the portal.

Dont know if it can be classified as a bug or unintended behavior but it is working as it should right now.

You will see that it works when using a Show in New Window option in the GTRR step.

[bruceR]

Write the script to do what you want. Example file updated.

Contact_Management.fp7.zip

Edited March 10, 2010 by Guest

[Larry Nichols]

Ah, sorry should have been more specific. Only applies to a GTRR that takes you to a different layout. The GTRR in your sample file uses the current layout, which naturally wouldn't trigger the onExitLayout.

This is file I'm testing with. The script set to fire onLayoutExit sets a variable $null with "". It makes the GTRR in the lineitems portal ignore the filter. Remove the layout-level trigger and the GTRR will take you to the filtered set (on a different layout).

Sorry if I'm not explaining this very clearly.

Invoices.fp7.zip

[bruceR]

Like I keep saying - write the script to do what you want.

It is understandable that the layout exit script loses the connection to the portal unless you explicitly establish it.

Invoices.fp7.zip

[Larry Nichols]

It is understandable that the layout exit script loses the connection to the portal unless you explicitly establish it.

Interrupting GTRR with a script triggered by onLayoutExit doesn't lose the connection to the portal, just to the filtered set. It still takes you to the record you selected, which obviously wouldn't happen if you just triggered the GTRR from outside the portal altogether.

I modify your script to go to a different layout and I still get my same result -- it takes me to the full related set, not the filtered set. If you can make it go to the filtered set on a different layout (with a script being triggered by onLayoutExit), I'm sure I and others would appreciate seeing the sample file.

[Larry Nichols]

Ah, never mind. I didn't see that you had modified my file. I just tried it with the Go to Object step at the end of the script triggered by onLayoutExit. That does fix it. Thanks so much.

[Jim Randell]

very interesting,

haven't tried this yet, haven't encountered the problem yet, but just reading the thread, it occurs to me that you can write the gtrr script to capture the current window name in a variable, then gtrr in a new window, then, close the window named in the variable (you might also need to capture window size and location)

[Atalanta]

I'm adding this here and hoping that someone has an idea.

I have a filtered portal that when Related::Field = "Yes" it will show the value. Now, count, as we all know, will count the records filtered or not. And a summary is lovely when the fields being summarized are in the table, not in a child/buoy.

The stickler for this, I cannot create a new field in the table I want to summarize. If I could, well that would be a cakewalk and I would be fine. I have to do all the calculations in a separate table.

I tried to create a calculation field: filterField in the parent/anchor with the calc: Filter (Related::Field; "Yes") but that returns nothing if the first row of the portal is "No" otherwise every value returned is "Yes".

I've been banging my head for the past two days on this stickler and every suggestion I've come across mentions somewhere "create a field in the child...." which I cannot do (it's not "our" database so I cannot modify it, only extract data from it).

Anyone?

Anyone?

Buhler?