Humm Variable relationship , Im stumped

[WSaxton]

Ok

 

So I have multiple tables set up to filter out data and show specifically the data for each different office that we have across the state everything works perfect in the following relationship

Table 1                                                                                  Table 2

Administration Board        -----------------------    Service Locations

Office location                                                   =  Office Location

 

This works perfect so the Administrators at different offices only see the service locations related to their Office and not all the rest

Here comes the question , I am working on an oversight board that the Directors of the company will be able to see all items for all locations or show each individual location so i created drop down list via the Office locations list, when I select any one of the locations the board populates exactly how it should

Where I'm stumped is showing all information combined not by location so for example

 

Value list is along the lines of -

All Locations

Location A

Location B

Location C

 

If I select Location A, B or C everything breaks apart like it should , any ideas on how to make the" All locations" show the data from all locations?

[Fitch]

There are several approaches you could take.

 

The simplest one might be to use a filtered portal:

 

1. Change your relationship to cartesion (select the "X" in the relationship options). This returns all records from the related table.

 

2. In the portal options, select "filter" and use a calculation like:

Administration Board::Office Location = "All Locations" or Administration Board::Office Location = Service Locations::Office Location

 

NOTE: This technique can be slow if you have thousands of records in the related table.

[WSaxton]

Thanks for the help

 

it has taken me a couple of days to get back to working on this problem I was able to take your advice and changed my relationship to an x so all records are showing I was able to get the exact results I was looking for by modifying your calculation to the following

 

If(Director_Board::Staff_Office_Selection="Combined Offices";Staff_Office_Selection ≠ Office ;Staff_Office_Selection=Office)

 

So now my portals filter by any of the office locations and show all the records if selected that's working awesome!

 

Now i'm stuck again. Previously in my portals I had a "Search" more of a filter really.  Here's what I was using below in combination with a firing script in the search field.

 

IsEmpty (G_Accounts) <--Global Field
or
Custom function---->Searchinplanttext(G_Accounts ;ACCOUNTS|ID_VAR||::Account & ¶ &ACCOUNTS|ID_VAR||::Status& ¶ & ACCOUNTS|ID_VAR||::Staff& ¶ &ACCOUNTS|ID_VAR||::Status& ¶ &ACCOUNTS|ID_VAR||::Billing_City ; 1 )

 

Either of these items by them selfs in the filter of my portals works beautifuly

 

Why wont they work together for example

 

If(Director_Board::Staff_Office_Selection="Combined Offices";Staff_Office_Selection ≠ Office ;Staff_Office_Selection=Office)

Or

IsEmpty (G_Accounts) <--Global Field
or
Custom function---->Searchinplanttext(G_Accounts ;ACCOUNTS|ID_VAR||::Account & ¶ &ACCOUNTS|ID_VAR||::Status& ¶ & ACCOUNTS|ID_VAR||::Staff& ¶ &ACCOUNTS|ID_VAR||::Status& ¶ &ACCOUNTS|ID_VAR||::Billing_City ; 1 )

 

So that if an office is selected the portal filters and even if your "search" filter again its by the "Search box"

 

Am I completely off base? like I said either one independent work beautifully its together they don't work only the if statement works.

[Fitch]

Sometimes it's easier to read and troubleshoot if you test each condition one by one, e.g.:

Case(
  IsEmpty (G_Accounts) ; 1 ;
  Staff_Office_Selection=Office ; 1 ;
  Director_Board::Staff_Office_Selection="Combined Offices" and Staff_Office_Selection ≠ Office ; 1 ;  
  Searchinplanttext( etc...; 1 ) ; 1;
 0 )