Filtering portal records based on global value not working

[Tom R.]

Hi,

 

I'm having some difficulty getting a portal to work as expected in the solution I've been working on. The solution is basically a catalog of different legal documents (like deeds, mortgages, and so forth). These documents are reviewed in the context of writing a legal opinion, so the solution also tracks opinions. You could think of an opinion as a project. So it's possible that a document could be reviewed multiple times in the context of different opinions, thus a many to many relationship between documents and opinions. So I set up a join table between the Document and Opinion tables. In the join table, I have fields for information like the review number and the status of the documents as far as it's related to the opinion. And of course, the foreign keys for the opinion and for the document.

 

My main layout is based on the documents table. I set up a portal to show records from the join table (opinion_documents), and set it to filter based on the currently selected opinion ID number. (When a user logs on, they select an opinion to work on and a script sets a global variable to that opinion ID, as well as a global field in the globals table.) So the portal is set to show only records where opinion_documents::_kf_opinion = Globals::CURRENT_OPINION_ID.

 

The behavior I'm getting from the portal is that the portal displays the correct record from the join table when a user first logs on and select an opinion to work with. However, if the user switches to a different opinion the record from the join table from the first opinion continues to display. So it looks like the calculation in the portal is not registering the change in the field Globals::CURRENT_OPINION_ID.

 

One other thing that may be relevant, is that this is a solution based on the data separation model. The globals table is in my interface file along with all of the relationships in the relationship graph.

 

I suspect my problem is that there isn't a field in the documents table that contains the ID of the currently selected opinion. I tried setting up a calculation field with global storage that was set to equal Globals::CURRENT_OPINION_ID, but I couldn't get that to work.

 

Any advice on this issue would be appreciated.

 

Thanks

 

Tom

[Opotoc]

Hi Tom,

 

 

two approaches you can try to resolve your issue:

 

1. Th fast one: Refresh the window incl. caches after you have changed the OPINION_ID global. If this doesn't work you can even try harder by setting the field the relationship depends on to itself (i,e, ID = ID) (there is a nice article in a German FileMaker magazine about this)

 

2. The elegant one: Personally I do not appreciate filtering portal much. Since you already work with FileMaker 12 you can easily set the contents of a global field  via ExecuteSQL

SELECT ID_of_Subtable from Subtable where ID=DocumentID and OpinionID=ID_you_want_to_filter.

Sounds complicated bit it isn't. Google and this forum is your friend.

If you have many records in you subtable this will have far better performance than filtering.

 

 

Best,

Alexander 

[Tom R.]

HI Alexander,

 

Thanks for the reply. Your tip on using ExecuteSQL sounds promising. I'm confused on one point - Should the ExecuteSQL statement be in the calculation field of the Filter Portal Records dialog? Or should this be done via script?

 

Thanks again,

 

Tom

[Opotoc]

Hi Tom,

 

Best practice is to use a script:

Set Variable [$theIDs] =  ExecuteSQL  ( ... )

Set Filed [ Your_global_Field_for_the Relation ] = $theIDs.

 

Cheers,

Alexander