portal sorted by two value lists

[kibber]

I have a portal (working perfectly thanks to mr_vodka) which filters and displays records based on their status (active, completed, canceled)-using global data.

Can I define it through an additional value list "class" being (maintainence, electrical, plumbing, painting or all)

The majority of the time it would be set to a default of 'all' but occasionally my boss would like to see if any one area is lagging.

As you can see I tried to just duplicate g.activestatkay2 with g.activestatkey3 but ran into the problem of the portal only being able to be referenced to one table at a time?

Thanks for your help

[mr_vodka]

Hi Kibber. Glad to see that you got it up and running.

Kibber, you can have gActiveStat3 matching to your class field. Then add this to the relationship you created for the Active key. in other words, you can have FileMaker match on both keys. Create another value list for the pulldown list for gActiveStat3.

[kibber]

That is perfect mr_vodka. I think I was mixing up the order of the values, so FM was looking to the wrong table for the data-if that is possible.

Only one issue though,

I have a value list:

maint.

plumbing

elec.

painting

can I easily create a value ALL

which will display values from the four categories above?

Thanks again for your help

Edited October 4, 2005 by Guest

[mr_vodka]

Kibber, sorry forgot about that you wanted an option for all records as well.

Okay there will have to be another field.

It will be a global calc field and you match on rather than gActiveStat3.

You will still need gActiveStat3 to allow users to choose from the value list on your layout.

Anway, the global calc field: gcClassKey

Let ( [ v =ValueListItems ( Get ( FileName ) ; "classitems" ); 

          s=Substitute ( v ; ¶ & "All"; "" ) ] ; 

            Case ( gActiveStatKey3 = "All" ; s ; gActiveStatKey3 ) 

      )

Basically when gActiveStatKey3 changes its value, his field changes its value to the value of gActiveStatKey3 if the value is not "All"

If not, it sets the field to the values of your value list, 'classitems'. Then it gets rid of the word 'All' from it.

Good luck.

[kibber]

I was having trouble posting yesterday, I can't seem to delete the 'new thread' I made yesterday - the blank one named "portal sorted by two value lists:revisited"- so if one of the moderators could do that it would be great

So I set up the gcclasskey field, made it a global calc based on your code mr_vodka;

put it in my WR layout and created a relationship between WR:gcclasskey & WR by ActiveStat:class

Then I made the gcclasskey field a pop up menu using the same value list as the WR:class value list

I think that is everything, so finally, I did something wrong because when I click on the WR:gcclasskey to sort the portal a pop up tells me the field is not modifiable?

Any thoughts? Thanks again for all the help

Edited October 6, 2005 by Guest

[mr_vodka]

No WR:gcclasskey should not be modifiable because its a calc field. This field just stays in the background.

When you have the multiple relationship of:

WR:gcclasskey = ActiveStat:class

WR:activestatkey2 = ActiveStat:status

When you change activestatkey3, the value of gcclasskey will change automatically and thus change your relationship values.

This way, when your user chooses the value for the Active field, and also choose to narrow down his search more by the Class category (activestatkey3, which i would rename Classkey), it will only show for example, all active plumbing records etc.

[kibber]

Got it, I am still having trouble with the whole 'background' field concept.

I have one final question...

When I create a new record my ClassKey field reverts to the 'active' status of my gActiveStatKey2 field?

So my display portal will go blank until I select 'All' or 'plumbing' etc.

Any thoughts on this one?

So I guess all that is left then is to enter the data...hehehe

Thanks again for your help John

[mr_vodka]

Since you copied the key field originally, it will retain the options from the orig field, being gActiveStat2. I think you copied it and called it gActiveStat3 right? Even though you renamed it Classkey, it still hold the option of the auto data entry value 'Active'.

So, go to youf field definition options for ClassKey, and change the Auto Data Entry from 'Active' to 'All' and you should be on your way.

Edited October 7, 2005 by Guest
typo