Filter portal by this calc

[Matthew R White]

Trying to filter the number shown on my portal by this calculation

 

Case (

Get (AccountName) = "Matt" ; TDL 2::Producer Code = 3;

Get (AccountName) = "Kim" ; TDL 2::Producer Code = 2;

Get (AccountName) = "Bill" ; TDL 2::Producer Code = 9;

else, show all.

)

 

But its not working

 

Thoughts?

[jbante]

What is happening instead of what you expect to happen?

 

Also, I presume your "else" condition in the actual calculation actually says "True" or "1", not literally "else, show all".

[Matthew R White]

I've figured it out!

I've created 4 separate tables, 1 for producer 3, 1 for producer 2, etc etc, and if Get (AccountName) = "Matt then the 3 other tables are (hidden)

 

This Works! However, it is seriously slowing down my system and object interaction. When I now enter the layout I get the (spinning wheel) and everything takes much longer and is slower. I'm guessing that having all these portals and filters is whats slowing down my system. Here is how 1 portal is calculated:

 

TDL 2::Producer Code = 3 and TDL 2::Completed ≠ 1 and TDL 2::Due ≤ Get (CurrentDate) + 1

 

Thoughts?

[comment]

...

Case (

Get (AccountName) = "Matt" ; TDL 2::Producer Code = 3;

Get (AccountName) = "Kim" ; TDL 2::Producer Code = 2;

Get (AccountName) = "Bill" ; TDL 2::Producer Code = 9;

else, show all.

)

 

Implementation aside, this is not a good idea because your solution should not need to be modified every time the staff changes (other than adding/deleting user accounts). Also, if this is a matter of controlling who gets to see what, it should be solved within user privilege set/s, not by any layout measures.

 

 

I've created 4 separate tables, 1 for producer 3, 1 for producer 2, etc etc,

 

Well, as you found out, that's not a good idea either.

[Matthew R White]

Comment: what other solution are you suggesting?

 

Thanks

[Rick Whitelaw]

What you're saying is that you'll create a new table for each producer. This could lead to an unending series of design changes. Every time there's a new producer there's a new table. Not a good idea.

[Matthew R White]

Rick, I understand that my current method of creating multiple tables isn't a good idea... I'm asking what is a better and more efficient idea?

[eos]

Instead of relying on account names (or building one table for each staff member – what does that table contain, anyway?), you should have one (1) user/staff/person/whatever table.

 

Let users log in against that user table to establish their identity within your solution (which should happen on top of FileMaker's built-in security, not instead of it).

 

This way, you need neither an unending series of tables (which you couldn't create automatically anyway), nor do you have to adjust any number of calculations like the on in the original post (that may be strewn all over the place).

 

Cache/store the user name (and/or role or other meta-data) to drive relationships or filter portals; then your original calc could become

 

$$userID = TDL 2::Producer Code

 

as a portal filter, or even better, a relationship predicate like

 

Control_Dashboard::gCurrentUserID = TDL_forDashboard::Producer Code

 

which will work regardless of the number of existing or new users.

[LaRetta]

Hi Matthew,

 

Do you always want their records to be restricted by this code?

 

If certain reps can only access certain Producer Codes, you might consider letting security handle it for you.  If you have a Users table, you can insert a field called ProducerCode.  Then after they log in, identify their user record (simple to do if their User record holds their Account Name) then do two things:

 

Set global variable with their user ID to use through your solution

Set global variable with their Producer Code

 

Then  ...

 

wow, Oliver just responded and it looks like he is on same track.  Let security restrict access and eliminate those other records automatically.

Well, we differ a bit ... I was going to suggest that the calculation be:

 

$$producer_code = TDL2::producer_code

 

But you get the drift, I think.

[LaRetta]

 

Cache/store the user name (and/or role or other meta-data) to drive relationships or filter portals; then your original calc could become

 

$$userID = TDL 2::Producer Code

 

as a portal filter, or even better, a relationship predicate like

 

Control_Dashboard::gCurrentUserID = TDL_forDashboard::Producer Code

 

which will work regardless of the number of existing or new users.

 

 

But ... if you use it in security, unauthorised records will not appear in portals at all.  That's the purpose of using security when you want to globally remove view of records.

[eos]

But ... if you use it in security, unauthorised records will not appear in portals at all.  That's the purpose of using security when you want to globally remove view of records.

 

Yes, you're right   – if one does want to remove them globally. We don't know that, though … maybe there's a portal for convenience, a list for completeness, and no calc to bind them all … 

[LaRetta]

I realised 'always filter' was probably the case since Matthew was going to put them in different tables but then, that's why my response began with the question to clarify ...  

 

As Michael (Comment) suggested originally, security works best at filtering 'by user.' 

 

Regardless, Matthew should now have ideas for moving forward.   :-)

[Matthew R White]

Ok... I think I follow... I'm very inept with regards to filemaker knowledge and our small business cannot afford a more experienced database guy... Thanks for bearing with me.

 

So I think this is what you are suggesting:

-On database open, run a script that designates $variables for the user id and producer code as a global variable.

-Re-write filter calculation for 1 table that filters based on $$producer_code = TDL2::producer_code which could better filter all of the records

 

LaRetta, to answer your question: Most of the time yes, in day to day operations i do want these records restricted by this code, however since we only have 4 agents or )producers) when 1 goes on vacation there will be a need to view his or her records as well tied to that account.

 

I do currently only have 1 table that defines users names, producer codes, etc etc.

[LaRetta]

Then I suggest that you create a value list of Producer Codes.  First value is the Producer Code ID and also display description as second value (if you wish).  Set a global field with the 'default' Producer Code for the staff person signing in upon file open - using their default Producer Code from their staff table.  Define a relationship as: 

 

your main layout table::global_producer_code = TDL2::producer_code

 

Place this portal on your layout (be sure the fields inside match this relationship).  Place the global above the portal and attach your value list to it.  In this way, the user default Producer Code will display but you will have the option of changing it via a popup on the global.  You can always use a filtered portal by setting a variable instead but it would require a method of allowing the User to re-define the global variable to change Producer Codes.  So since you need user interaction on the selection, best to provide the global from the beginning.

 

Let us know how it goes. :-)

[LaRetta]

Another consideration folks make regarding filtering a relationship is whether to provide an -All- to see all related records.  Personally, I do not like this method as it adds a burden onto the solution unnecessarily (in most cases).  It is same as showing all records when switching to a layout - I dislike it because it pre-fetches when most times a User wants to see only a sub-set.  It is best to enter find mode before switching to the layout, entering a bogus value, finding nothing, stifling the error and showing NO records.  This is light-weight.

 

So on portal, if you wish to see multiple Producer_codes, you could use checkbox or different technique.  I could not provide more specific suggestions without understanding more of your business logic and user process.