Go to related record with an OR operator?

[kmninbox]

Hello, I am pretty much a newbie to FMP, using FMP 11

I kind of figured out the go to related record script...

however, my situation is : I have 3 fields that contain account name in my CustomersTable; CreatedBy (autoenter), CurrentSalesRep (which I assign an account name to a customer), and CurrentUser (a calc field with Get (accountname)).

Right now I have a self join on the relationships graph where CurrentUser in CustomerTable is related to CurrentSalesRep in CustomersTable2 (same table but self joined). When I run the go to related record script, it works fine, and shows shows me all customers assigned with the currently logged in user.

However, when I have my SalesReps accounts create new customers, they are not assigned to an actual account name yet, so CurrentSalesRep is empty, what I really want the go to related record script to do for me is to use both CreatedBy and CurrentSalesRep fields to see if either contain Get (AccountName) as true, and then go to all records that have either CreatedBy as the current account name, or CurrentSalesRep has current account name.

I tried another way with using enter find mode, and set field, but again, I can't seem to add an OR operator to include both fields, or when I try to do the expand found set step in the script, I can't seem to figure it out...

Basically, I have privilege sets for SalesRep accounts and when they log in, I want them to be working from a found set that is related to by either CreatedBy or CurrentSalesRep only, I don't want them to see other SalesReps customers, and I don't want them to have to search through a whole bunch of "no access" records.

Please help.

[bcooney]

There are probably a number of ways to approach this...

First of all, you do not need a calc field that equals CurrentUser. Get (AccountName) will always evaluate to the account that logged in (just like Get (CurrentDate) always equals the date on the workstation clock, "today").

Your Security rule should allow view of records where CurrentSalesRep = Get(Accountname) or IsEmpty (CurrentSalesRep).

Then, use Find. With the rule in place, you should be able to Find on the Customer Key field (that is CustID=*) and the rule will take care of just showing the records for that Get (AccountName) and the unassigned.

Take a look at Ray Cologon's demo to truly understand RLA.

[comment]

The Go to Related Record[] step goes to related records. If you want the result to include more records, you should solve this by making more records related.

I am not sure I fully understood your requirement but I believe one of these should work: define a new calculation field (result is Text) =

List ( CurrentSalesRep ; CreatedBy )

and use it as the matchfield on the CustomersTable2 side. This will show all records that match the current account either in CurrentSalesRep or as CreatedBy.





The other option is to make the field =

Case ( IsEmpty ( CurrentSalesRep ) ; CreatedBy ; CurrentSalesRep )

This will exclude records created by the current account but assigned to another.

Note also that GTRR will automatically exclude records for which the user has no viewing privileges. If you privileges are set correctly, you could even make the relationship match ALL records - and GTRR would still filter them down.

First of all, you do not need a calc field that equals CurrentUser.

Well, you do, if you want to use it in a relationship.

[kmninbox]

Thank you both very much for answering. I will try each suggestion to see which one will work best for me in the long run. I am sure either one is a much more elegant and fail safe(er?) solution than what I can think up at the moment. For now I am working with a rudimentary script that I found from another post, it allows me to find Get(AccountName) in either CreatedBy or CurrentSalesRep and displays them in a found set without all the extra no access records.

Enter Find Mode []

Set Field [CustomerTable::CreatedBy ; Get ( AccountName ) ]

New Record/Request

Set Field [CustomerTable::CurrentSalesRep ; Get ( AccountName ) ]

Set Error Capture [on]

Perform Find []

"

The other option is to make the field =

Case ( IsEmpty ( CurrentSalesRep ) ; CreatedBy ; CurrentSalesRep )

This will exclude records created by the current account but assigned to another."

yes thank you for pointing this out, I am really only feeling my way through the solution logic as I go...yes, a customer can be created by one account, but be assigned to another account as CurrentSalesRep, in that case, I would not want the account that created the customer to have access any longer. Will give it a try.

[kmninbox]

I'm already tripping over myself...

I tried the second solution you suggested, defined a calc field that returns text result, and I also checked do not store in storage (I'm not sure why, but it makes it work better for Get(AccountName)),

... calculation: Case ( IsEmpty ( CurrentSalesRep ) ; CreatedBy ; CurrentSalesRep ), and I suppose the calculation works and returns the correct results.

Now, on the relationships graph from CustomerTable I drag this new calc field to CurrentUser field in CustomerTable2, or is it vice versa? Either way, I am getting the error

This operation cannot be performed because one or more of the relationships between these tables are invalid when trying to run the script Go To Related Records.

In the Go To Related Record Script I have : Go to Related Record [show only related records; from table: "CustomerTable 2"; Using layout: "Layout" (CustomerTable)]

When you say use it as a match field, which other field (CreatedBy, CurrentSalesRep, or CurrentUser) should I match it to, and from which table occurrence to which table occurrence?

[comment]

The calculation MUST be stored, otherwise it cannot be used as a matchfield on the "child" (CustomersTable2) side. OTOH, the CurrentUser calculation must be unstored.

When you say use it as a match field, which other field (CreatedBy, CurrentSalesRep, or CurrentUser) should I match it to, and from which table occurrence to which table occurrence?

CustomersTable::CurrentUser = CustomersTable2::NewCalculationField

This is assuming the layout from which GTRR is called is showing records from the CustomersTable TO.

[kmninbox]

I've made the changes as your instructions, and it seems to be working great! THANK YOU SO MUCH

now to get started on the 90% work left to finish my total business solution.