List () Through a Filtered Portal

[Ron Cates]

Good Morning All,

I am working on a task tracking system that includes a portal to a join table. I added a portal filter to be able to narrow down the returned records by catagory and status. On the layout are a set of fields for viewing a record when selected in the portal. Pretty standard stuff. I only recently started using FMPA11 so this is actually my first experience with 11s portal filters. Although I have read a lot about them and do understand the difference between a filtered portal and a filtered relationship. That being a filtered relationship only allows through the records that match it's criteria where a filtered portal brings all valid records through the relationship and then applies the calculation to narrow down what is actually viewed. This is evident when using the List () function to try and get a list of related record IDs. With a pure relationship filter, List () returns exactly the same records viewed in the portal. When using a portal filter List () returns the related records before the calculated filter is a applied.

So here is what I would like to do. On the layout i described, a user selects a portal row which sets a global to the task ID of the record to be viewed in the fields next to the portal. Now, if the user then selects the dropdown and changes the filter criteria the records viewed in the portal change and the new set may not include the record the user still has selected. At that point I would like to clear the global ID field so that record details can not be viewed if the record is not included in the filtered set of records viewed in the portal. I would actually like to set the ID to a global variable so that if the user then reselcts a filter criteria that puts the selected record back in the portal then I would reset the global ID and reestablish the previously selected record.

So, all this comes down to one sticking point. How do I get a list of IDs returned after the portal filter to test to see if the selected record is included in the records being displayed in the portal?

[David Jondreau]

A few methods come to mind.

1) Change the portal filtering to relationship filtering.

2) Use a custom function that grabs the filtered record set's IDs TypeSumField() is a starting point.

3) Use conditional formatting to build the list of found IDs. List ( $$IDs ; portal::id ). Caveat here is clearing the $$var and you need every record visible. If the portal isn't tall enough you can build a second "invisible" portal with the same filtering criteria showing all the records.

[comment]

So, all this comes down to one sticking point. How do I get a list of IDs returned after the portal filter to test to see if the selected record is included in the records being displayed in the portal?

Actually, it doesn't have to come down to that point: you could simply test the selected record using the same criteria as the portal filter does (even place it in a one-row portal of its own, using the same filtering as the 'main' portal).

In order to get the list of portal values, you can use the GetLayoutObjectAttribute() function within a recursive custom function or a repeating calculation field.

[Ron Cates]

1) Change the portal filtering to relationship filtering.

I always have used filtered relationships because I was on FMPA10 until only recently. But in this case I was hoping to take advantage of 11s filtered portal because it's a join table setup.

Users ---< Users_Tasks >--- Tasks

The Portal is viewing records from the join table ( Users_Tasks ) but the filter criteria is based on fields in Tasks. So to use a relationship filter I would have to add the Catagory and Status fields to Users_Tasks and script to keep them syncronized with the same fields in Tasks. This is how I have done it in the past.

2) Use a custom function that grabs the filtered record set's IDs TypeSumField() is a starting point.

3) Use conditional formatting to build the list of found IDs. List ( $$IDs ; portal::id ). Caveat here is clearing the $$var and you need every record visible. If the portal isn't tall enough you can build a second "invisible" portal with the same filtering criteria showing all the records.

Looking into these options...

[Ron Cates]

In order to get the list of portal values, you can use the GetLayoutObjectAttribute() function within a recursive custom function or a repeating calculation field.

Giving this a try. This is the first custom function i've written so be gentle

I gave the Users_Tasks id field in the portal an object name. Then I created a custom function called FilteredPortalList ( fieldobjectname ; portalrow )

The calc in the function goes like this;

Let ( [

current = GetLayoutObjectAttribute ( fieldobjectname ; "content" ; 1 ; portalrow );

portalrownext = portalrow + 1

] ;

Case ( not IsEmpty ( current ) ; current & "¶" & FilteredPortalList ( fieldobjectname ; portalrownext ) )

)

It seems to work ok but i would like to eliminate the trailing carriage return i'm left with at the end.

So, am I on track here? Did I do ok writing the function? If so, how would I eliminate the trailing return?

Thanks

Ron

[dansmith65]

FYI: another way to access filtered records in a portal is via GetNthRecord() when a portal row is active.

[imoree]

Giving this a try. This is the first custom function i've written so be gentle

I gave the Users_Tasks id field in the portal an object name. Then I created a custom function called FilteredPortalList ( fieldobjectname ; portalrow )

The calc in the function goes like this;

Let ( [

current = GetLayoutObjectAttribute ( fieldobjectname ; "content" ; 1 ; portalrow );

portalrownext = portalrow + 1

] ;

Case ( not IsEmpty ( current ) ; current & "¶" & FilteredPortalList ( fieldobjectname ; portalrownext ) )

)

It seems to work ok but i would like to eliminate the trailing carriage return i'm left with at the end.

So, am I on track here? Did I do ok writing the function? If so, how would I eliminate the trailing return?

Thanks

Ron

Case ( not IsEmpty ( current ) ; current & "¶" & FilteredPortalList ( fieldobjectname ; portalrownext ) ) 

Havent tried it but i have a feeling that you need to just enclose in the list function

eg 

 list ( current ; FilteredPortalList ( fieldobjectname ; portalrownext ) ) 

may be it.

Edited March 6, 2012 by imoree

[comment]

I don't think a trailing return is necessarily a bad thing (all the native xValues function have this "feature"), but if you want, you could do it this way =

Let ( [

current = GetLayoutObjectAttribute ( fieldobjectname ; "content" ; 1 ; portalrow ) ;

portalrownext = portalrow + 1

] ;

Case (

not IsEmpty ( current ) ;

List ( current ; FilteredPortalList ( fieldobjectname ; portalrownext ) )

)

)

Note that your method assumes the listed field is never empty, otherwise your "chain" will break.

[Ron Cates]

Thanks guys, that's awsome!

I do understand that the function as it's written will only work if the field has a value in all records, such as an id field. But if i wanted to make it work on a field that may be empty in some records, how would i control the recursion and tell the function to stop after the last record in the portal? Is there a way to count the portal records first?

[comment]

Is there a way to count the portal records first?

There is, though you could just as well count the (unfiltered) related records and count down from there.

[Ron Cates]

So if I had 12 records showing and I counted 20 records through the unfiltered relationship would that mean that after the 12 recursions I need, the function would got through 8 more before it returned it's final result?

I'm sure there would be no performance issue on the small record sets i'm working with and frankly I don't see ever using a Portal Filter on a portal that would filter a very large set of records. So i can see the end result would be the same, but I somehow feel that if there's a way to count the filtered records in the portal, that would be the way to go for best structure.

[Ron Cates]

I've run into another issue. I'm using UUIDs for primary keys. But this seems to be causing problems.

Where the id ( number ) field contains 1-2-063465939548-0000000-12414-233916158973329

The GetLayoutAttributed function returns 1.2063e+40

[comment]

So if I had 12 records showing and I counted 20 records through the unfiltered relationship would that mean that after the 12 recursions I need, the function would got through 8 more before it returned it's final result?

In my version it would run 8 empty recursions first, but essentially the answer is yes.

if there's a way to count the filtered records in the portal, that would be the way to go for best structure.

It's the same way as what you're doing now: place a summary field in a filtered portal, give at an object name and get the value through GetLayoutObjectAttribute().

Where the id ( number ) field contains 1-2-063465939548-0000000-12414-233916158973329

The GetLayoutAttributed function returns 1.2063e+40

Looks like a data type mismatch.

[Ron Cates]

The UUID field is a number field. I've been puzzled as to why there are dashes in the number. but in match fields it seems to match wether the dashes are there or not.

For instance when i set a global match field with the id it first displays like this

1.2063e+40

when i click in the field it displays like this

12063466119277000000008714233916158973329

When I put the ID field on the layout it first displays like this

1.2063e+40

when i click in the field it displays like this

1-2-063466119277-0000000-08714-233916158973329

But these two fields do match for a valid relationship

[Ron Cates]

Well if I change the ID field to text it works fine. So now I guess I'll have to decide wether I want to use these UUIDs. The info suggests that the ID fields should be number. I don't know what the result of having them as text would be except that there was a discussion about a performance hit.

[Ron Cates]

Followed up by posting where I got the UUID custom function to ask about the bove issue. The answer was

"If you edit the idField object in layout mode to use the "As entered" number format (Inspector palette > "Data" tab > "Data Formatting" section), you should be able to get the full UUID."

It worked. I'm back on track.

Thanks All :)

[comment]

"If you edit the idField object in layout mode to use the "As entered" number format (Inspector palette > "Data" tab > "Data Formatting" section), you should be able to get the full UUID."

I don't know about that. I assure you that "1-2-063466119277-0000000-08714-233916158973329" is NOT a number. Filemaker is pretty tolerant about storing text values in Number fields, but it is not a good policy in general.

[Ron Cates]

I agree. I'm trying to start off a new database and i've been following the standards discussed at http://filemakerstandards.org/display/cs/Overview. I'm becoming a bit concerned about this UUID system they recommend. I don't really understand why it is generated with the dashes to begin with. There's more discussion on it here http://filemakerstandards.org/display/bp/Key+values.

I did adjust the custom function to go with a count of the (unfiltered) related records. So once I had my list of IDs I was able to test to see if the selected record was in the list.

I achieved my end goal of being able to clear the selected record if the filter criteria was changed to exclude it from the portal and restore it if the filter was changed again to include the selected record back in the portal.

Guess i'll have to take a good hard look at wether I want to go with these UUIDs before I get much further on this project.

[comment]

With all due respect to all involved, calling yourself a standards organization does not automatically make you one. Neither does the claim that "the general consensus for these documented standards is to use a universally unique identifier (UUID)" hold any significance that I can see.

IMHO, UUIDs are useful in situations where the creation of serial numbers cannot be controlled - for example, data being collected by isolated individuals. It should also be noted that the format of a UUID is of no importance as such. Filemaker does not recognize numbers in hexadecimal notation, so it's kind of pointless to mimic the format. The Version 4 (random) of the UUID standard calls for a random value generated out of 5316911983139663491615228241121378304 possible values. This may seem like a very large number, but it's practically nothing compared to Filemaker's maximum of 10^400.