Jump to a record in Portal

Title

Jump to a record in Portal

Post

I have setup a portal that will keep track of the salaries paid to our staff, we 500 staff in 6 different locations. The portal will show all the staff in different locations and what has been done to calculate their salary during that month.

However there are up to 100 staff in each location and to scroll down the portal to find the staff's name to update their salary is time consuming.

I would like to create a value list above the portal, and the clerk can easily type the first few letters of the staff's name, click on the correct name and then with a script trigger it would move to the portal field containing the staff's name.

This can be down using a global in your LOCATIONS table and a calculation on each side of the relationship and then modifying your current relationship to include a match between the two calculations.

I am guessing you are looking from your Locations table at a list of Staff related to that location, if this is wrong then modify the description bellow accordingly.

So start by creating a new field called gNames in your LOCATIONS table, change the storage of that field to be a global, in the field options under the Storage tab. And also create a calculation called cPortalFilter in the same table, and the calculation should be as follows:

If ( IsEmpty ( gNames ) ; "All" ; gNames )

And in your STAFF table you need to create a calculation that is called cNameFilterKey and has the following calculation

"All¶" & FullName

Now modify the relationship in you Relationship diagram so the LOCATIONS / STAFF relationship looks something like this:

LOCATIONS Locations_STAFF_PortalFilter

_UniqueID --=-- _LocationID

cPortalFilter --=-- cNameFilterKey

You will also need a second relationship between these two tables to make the Drop-down value list only display staff that are related to the LOCATION record you are viewing which will simply be between the LocationID field

LOCATIONS Locations_STAFF_LocationID Match

_UniqueID --=-- _LocationID

Now place the gCategory filled on your layout and make it a Drop-down Menu with a value list based on the StaffName from the STAFF table, this is done by creating a new value list 'File > Manage > Value LIsts...' and 'New' and then click 'Specify field...' next to 'Use values from field:' selecting the FullName field from your Locations_STAFF_LocationID Match table and make sure 'Include only related values starting from' is selected and you choose the LOCATIONS table from the list.

When setting up the field to be a Drop-down also check the option 'Auto-complete using value list' and now in browse mode you can click in the field, start typing and when you click out of the field, or select a name from the drop-down and the portal will refresh, no need for a script trigger in this case.

You can get more advanced with this and make it filter as you type, but you need to expand the cNameFilterKey field and include a script trigger as you type into the gName field, if you want me to go into detail on how to do this then let me know.

The way it works is on the STAFF side you have a Multi Key that has two values, "All" and the Full Name, on the locations side the calculation looks at the global and if there is a value it will display that value, matching with the record in the STAFF table that has the same value in the calculation, otherwise it will have "All" in it therefore matching with all the records in the staff table.

Let me know if anything is unclear and I will explain more for you, I have also put up a quick demo that I put together while writing this for you to look at and take apart: