Filtering a Portal List

[Bryan VonDeylen]

I have a Stockroom solution (FM8) that I am trying to figure out how to filter a portal list.

User Account Table

School Table

Stock Requisition Table

Of course, there are other tables, but they don't play into this at all.

From a layout using the Account Table, I have a portal showing the Stock Requisitions.

I have setup two global fields in Accounts. One for School ID and one for Account #.

My relationship is Accounts::SchoolID = Stock Requisition::SchoolID

AND

Accounts::Account# = Stock Requisition::Account#

BUT, this requires that I have the contents of BOTH School and Account # completed for the list to show.

Would I would like:

1) Select school to give a list of ALL stock requisitions for the school

2) IF I want to further narrow the list by Account#, select an account# to only show those stock requisitions from that School with that account #

I can do #2, I can't do #1.

If I eliminate the Accounts::Account# = Stock Requisition::Account#

I can then do #1, but I can't do #2

I want to do both.

Has anyone tried this and been successful?

Again,

Select School to show all stock requisitions from that school. (Global Acct# field is empty)

Select Acct# to further narrow to show all stock requisition from that school using that acct#.

thank you,

Bryan VonDeylen

Neenah Schools

Neenah, Wisconsin

[Ender]

Hi Bryan,

Your discription is a little confusing:

I can do #2, I can't do #1.

If I eliminate the Accounts::Account# = Stock Requisition::Account#

I can then do #1, but I can't do #2

I want to do both.

Do you mean you want to allow matching by account# if an account# is entered (with School ID blank) and allow matching by school if a School ID is entered (with account# blank,) and match by both if both are entered?

[Bryan VonDeylen]

No, I want to be able to

Select a School and get all Requisitions for that school (no matter the account).

Then if I click an account, only get those requisitions that match both School and Account.

So, I want my choices to be either

1) Pick school and get all requisitions for the school

2) Pick school and account and get all requisitions that match BOTH school AND account

currently, if I pick school, I get nothing unless I also pick an account. Once I pick an account I do get those requisitions that match both School and Account. I am limited though, in that I cannot get ALL requisitions for a school.

If I change my code, I can pick a school and get all requisitions, but if I pick account nothing changes, I still get all requisitions.

[comment]

Try changing your relationship to:

Accounts::gSchoolID = Stock Requisition::SchoolID

AND

Accounts::gAccount# = Stock Requisition::cAccount#

where cAccount# is a calculation field (result is text) =

Account# & ¶ & "All"

Change your Accounts value list to show values from the field cAccount#. Select "All" to view all requisition of a school regardless of account, or select an account# to narrow it down.