Two-dimensional logic test in a calculation?

[DataCruncher]

Wow. I was unable to make this work - I need someone to help guide me in the right direction, I figure it should be easy enough. 

Here is an example of what I need to achieve:

- Imagine a simple online ordering system. 

- I have a table called Assignment where I have records with two fields: Assignment::Client and Assignment::Agent. The idea  is every agent can work with only certain clients. I use the agent's account name to create this table. If a matching Client-Agent record exists, the Agent shall be able to see orders from that client; if no such record exists, then the agent should not see that client's orders. Throughout my solution, I use $$accountname for the filemaker username. 

- In my Orders table, a new incoming order does NOT have an agent assigned yet. Orders::Agent is empty. That means I can't yet establish a relationship to decide which orders to show to an agent. 

- I need to create an order inbox where every agent should only be able to see the new orders from Clients that agent is 'credentialed' for in the Assignment table. 

 

So these are the approaches I have considered already but was unable to get to work:

- A relationship to the Assignment table. This approach does not work as the incoming case does not have the Orders::Agent field populated yet. 

- An ExecuteSQL statement which, in theory, should work but as I have 300k records in my database, I found it slows down FileMaker Server too much for some reason.

Plus, I did not get it to work properly. I tried 

ExecuteSQL ( "SELECT COUNT ( ID ) FROM Assignment WHERE Client=? AND Agent=?" ; "" ; "" ; Orders::Client ; $$accountname )   

but this returns '?' only

 

I am sure there must be an excellent and straightforward way to get this to work, but I am at a loss. 

 

Thank you for any suggestion!

 

DataCruncher

 

[comment]

I am not sure I understand the problem. AFAICT, you want to allow an agent to see an order when:

Orders::Agent = Get ( AccountName )
or
IsEmpty ( Orders::Agent )

 

[DataCruncher]

The first part is correct. However, in the second part when Orders::Agent is empty, record should ONLY show what that oder is from a Client that would match a specific Assignment::Client-Assignment::Order record. 

 

If I have agent Senior and agent Junior, Senior may see all orders from Client A, B, C, D, E but agent Junior may only see orders from Client C and E. 

The Assignment table would thus contain records:

Agent - Client

Junior - C

Junior - E

Senior - A

Senior - B

Senior - C

Senior - D

Senior - E

 

I hope this outlines my problem more clearly? 

 

Thank you!

 

 

[comment]

Not quite. What are "Senior" and "Junior"? Are they account names or privilege set names?

Earlier you said:

3 hours ago, DataCruncher said:

A relationship to the Assignment table. This approach does not work as the incoming case does not have the Orders::Agent field populated yet. 

But clearly the relationship between Orders and Assignment is - or at least should be - based on matching ClientID? Either directly or through a Clients table? 

 

 

[DataCruncher]

Well I fixed it!

It was a little workaround - I basically broke it down in two steps: 

1) establish a relation between Client in the Orders table and Client in the Assignment table. 

2) Then use ValueCount ( FilterValues ( List (Assignment::Agent) ; $$accountname ) ) to filter the related records...

 

I hope this helps someone. 

[comment]

2 hours ago, DataCruncher said:

I hope this helps someone. 

Anyone reading this should ask themselves what is the purpose of this exercise: is it user convenience or is it data security? If it's the latter, than a solution relying on portal filtering (or any other layout-level device) will not be adequate.