Dynamic Portal Filter

[rkass068]

Hi I have been searching all over the net on how to do this and I am getting some info but I cannot piece things together. I am trying to filter one of my portals to summarize data based on a radiobutton set. The portal is displaying info from the table "Visits". Each record has a format set to it either as "Lab", "HSCT", or "treatment". I have global field that will be the radio button set which contains the same values as the Visits::Format field. I want the portal to filter the data based on the global field; however it wont seem to work when I do this script:

 

 Freeze Window

Commit Records/Requests

Refresh Window [flush cached join results] 

 

My filter Calculation is:

 

Each portal record will be visible when:

 

Visits::Format = Main:gfilter

 

Can someone tell me if there is a problem with my calculation or script? The portal is blank when I set portal filtering on with what I have now.

 

Thanks

 

[LaRetta]

That should work fine if the Visits field contains only one value.  You might verify that is the case.  Also verify that your global is text and that it IS global.  Without the portal filter, does the relationship between the two tables normally produce results?

 

Added blue

[rkass068]

That should work fine if the Visits field contains only one value.  You might verify that is the case.  Also verify that your global is text and that it IS global.  Without the portal filter, does the relationship between the two tables normally produce results?

 

Added blue

Yes, the visits field only contains one value and the global field is a text field. Without portal filtering the portal does produce the results. I have no idea why it wont work. do I need to put anything in "" marks in the calculation. Does my global field have to be in the Visits table?  I will keep searching to see if anything is not allowing the results to be produced. 

[LaRetta]

Nothing needs to be in quotes since you are referencing only two fields.  No, the global field an be anywhere in your file (or even in another file) as long as it is in a table occurrence in your graph.

 

Have you made sure that the Visits field is NOT number also?  Otherwise it would be best to post your file; no need to search, it should be working so there is something amiss in your file. 

[rkass068]

Thanks LaRetta for your help. Most stupid thing happened. In my value list at the end of Lab, HSCT and Treatment there was a space at the end of each word..... That is way to sensitive. Works great now thank you!

[LaRetta]

Ah yes, the ole space ... been there ... done it.  It is good to add field-level validation that it is member of value list.  This is particularly important to validate (as you realise) if you are using drop-down for selection since Users can easily make typing mistakes.  Glad it worked out for you!!

[rkass068]

Ah yes, the ole space ... been there ... done it.  It is good to add field-level validation that it is member of value list.  This is particularly important to validate (as you realise) if you are using drop-down for selection since Users can easily make typing mistakes.  Glad it worked out for you!!

 

Hahaha thanks! So taking this further, is it possible to maybe make the global field a checkbox and allow the portal to filter for more than one value. For example if I check both Lab and treatment, the portal will display the records that contain "lab " or "treatment" in the Visits::format field? Sorry to bother you again. Thanks for the help.

[LaRetta]

No bother.  Yes that will work.  You may need to nudge it so it refreshes particularly if you are using separation.  If it doesn't refresh as you check an item, use script trigger OnObjectModify.  :-)

[rkass068]

No bother.  Yes that will work.  You may need to nudge it so it refreshes particularly if you are using separation.  If it doesn't refresh as you check an item, use script trigger OnObjectModify.  :-)

Thanks, do you know how abouts I can do that. I know that I will need to change the filter Calculation. It is currently 

 

 

Each portal record will be visible when:

 

Visits::Format = Main:gfilter

 

How would I include more than one Visits::format entry to be displayed? Thank you

[peaceandplenty]

I think this thread also answers a question I had but I want to restate it in 'novice' terms (please forgive me if I am hijacking, I'll start a new one!)

 

Say I searched for all Florida zip codes in an address DB, and say I'm looking at a single record in Form View. I want to have a list on the Form View that shows the entire found set. I also found this which seems pretty close to what I want: 

 

http://fmforums.com/forum/topic/48079-calculation-to-list-current-found-set-record-ids/.

 

But perhaps there's an easier way to do what must be a pretty common thing? Filtered portals, or? Thanks.

[LaRetta]

My apology for not including a revised filter calc.  It would be:

 

not IsEmpty ( FilterValues ( Main::gFilter ; Visits::Format ) )

 

But really, I would suggest just including the global to visits in the relationship filter if possible.  Portal filters can drag on resources and it sounds like the Visits table might get up there in numbers, plus I prefer to have portals empty (or at least restricted to most-viewed records) until selections are made.  This aids in system speed when they don't have to load portals as Users are scrolling through records and working.

[LaRetta]

Hi PeaceAndPlenty,

 

ExecuteSQL() might be good way of collecting the IDs then set a global and relate to another occurrence of that table.  Portal filter calc I presented would work with zips also but again I recommend using old-fashioned relationships when possible. Why not set the left with the Florida zip and the right with the table's real zip?  The results would be very, oh please forgive ... ahm ... zippy.

[peaceandplenty]

Well. Who would ever think that humor and database programming could actually hold hands? 

 

I'm sure you answered the question but I didn't quite grasp what you are saying, so I thought I'd use a different example and describe what I am trying to do more fully: 

 

I've got an extensive book library and, naturally, a FM database that keeps track of my holdings. Every title has a record. So far, so good. Just like any library card catalog, not complex.

 

I'm looking at a simple Form View that shows a record for each book.

 

Now, let's say that I performed a search on the 'Author' field looking for  'Kim Stanley Robinson'. I have more than one book by this author. FM would show me the first matching record, allowing me to scroll to subsequent record that match the search criteria. So far, so good. 

 

Keep in mind that the FoundSet includes 'Red Mars', 'Green Mars', 'Blue Mars' and '2312' and that I am viewing them one by one in Form View by simply advancing the record.

 

I could also have a List View that shows all members of the FoundSet, but this list would need to be a different window and different layout.

 

I want the list of matching records to be on the Form View layout.  This way I can see all of the holdings by Kim Stanley Robinson, holdings that are otherwise visible only by scrolling, or by switching to a different layout.

 

 

Maybe?

 

(My skill level is really novice).

[rkass068]

I think this thread also answers a question I had but I want to restate it in 'novice' terms (please forgive me if I am hijacking, I'll start a new one!)

 

Say I searched for all Florida zip codes in an address DB, and say I'm looking at a single record in Form View. I want to have a list on the Form View that shows the entire found set. I also found this which seems pretty close to what I want: 

 

http://fmforums.com/forum/topic/48079-calculation-to-list-current-found-set-record-ids/.

 

But perhaps there's an easier way to do what must be a pretty common thing? Filtered portals, or? Thanks.

 

 

I think this thread also answers a question I had but I want to restate it in 'novice' terms (please forgive me if I am hijacking, I'll start a new one!)

 

Say I searched for all Florida zip codes in an address DB, and say I'm looking at a single record in Form View. I want to have a list on the Form View that shows the entire found set. I also found this which seems pretty close to what I want: 

 

http://fmforums.com/forum/topic/48079-calculation-to-list-current-found-set-record-ids/.

 

But perhaps there's an easier way to do what must be a pretty common thing? Filtered portals, or? Thanks.

Thanks for hijacking my thread its okay tho my question was answered so hijack away. 

[LaRetta]

Hi PeaceAndPlenty,

 

You have referenced the correct thread then.  However you achieve the found set, the IDs still need to be written to a global field and used in the relationship to another (or in this case same) table to provide the portal display.  Also read the thread referenced at the end of the first thread.  

[peaceandplenty]

Thanks for hijacking my thread its okay tho my question was answered so hijack away. 

 

Sorry dude! I wasn't sure if I was asking the same question that you had when I rudely jumped in. Still not sure if the question has been answered but apologize for thread-jacking…