WebDirect – Value List Modification

So, value list modification in the FileMaker client software is easy – it’s just a check box you can tick to give easy modification access. However, in a WebDirect solution, there is no easy way to do it. As a developer, you definitely DO NOT want to have to modify a value list every time a client wants it changed. It’s simply not worth your time.

The basic idea of this is that you use a related table and a filtered-related value list calculated between two tables to pull related records from the VL table in order to simulate a modifiable value list.

Here is my simple, effective work around.

We’ll start with the premise that you want your clients to be able to chance the “type” of person in a Person table in a WebDirect solution.

To begin, we’ll have two tables: Person and VL – short for Value List.

relationsihp

The Fields are fairly straight ahead.

Person::VL_1 is a calc field (Globally Stored) which just has ‘1’ as the calculation, and is evaluated as a number.

Filter::g_filter is just a Global text field which will be used later for filtering portals

Now, we add a self-join table for VL, which is a Full Outer Join if I’m not mistaken.

relationsihp2

This allows us to look at all the values in the VL table via a portal looking at the Table Occurrence of VL_VL.

The layout I’ve created looks like this, but obviously yours will probably look different depending on your taste, theme, etc.

VL_layout

The “p” field you see up in the corner is my filter field – in this example it would be the g_filter.

I have it sorted alphabetically, but the filter is simply VL_VL::VL_num = VL::g_filter

The “New Value” button copies the number in the filter and makes a new record in VL_VL and pasted the filter number into the VL_num field – this assures that the proper related record will be filtered.

Now we’ll make a new value list. Generally I call them something like VL_#_TOfield where # is the VL number assigned to the VL set ( VL::VL_num), TO is the Table Occurrence prefix, and field is the field. So, in this case, for the Person::type field, I’d call it VL_1_PERtype

Select the first bullet “Use Values From Field” and then Specify Field. Select the VL Table Occurrence coming off the Person table, and from the left field-select box, pick the “value” field

Select the second bullet below the field-select box, “Include Only Related Values Starting From” and then select your Person table.

Now, when you add values to the VL table, and give the record the VL_num of “1”, the values will show up in the value list!

This simple work around for modifying value lists in WebDirect takes all of about a minute to implement when you get the hang of it and will save you lots of time and headache. The people using your WebDirect solution will also thank you!

Let me know if you’ve got any questions of comments!

-NCH

FileMaker Dynamic Portal Filtering for Web Direct

Web Direct is becoming increasingly popular with FileMaker developers as it is easy to implement and in many cases, faster than FileMaker client pulling data from FileMaker Server.

There are many examples of how to do dynamic portal filtering which are highly effective using the FileMaker client software, but don’t work so nicely in a Web Direct solution. This is caused by a Script Trigger on the “Filter” field which commits the record, thus updating the portal. In a Web Direct Solution, if you enter characters into the field faster than ~ 15 WPM, you’ll get lag and your characters can be entered out of order.

Here is my solution:

script_TriggerRefresh

Now, this script is used with a custom function – which I did not write – called
“IndexWord” which takes two arguments, ( Text, Header )  where Text is the word you want to call the function on and Header is just “” – double quotes.

Let(
[
tCount = Length(Text);
newHeader = If(Header ≠ “” ; Header & ¶ );
NewText = Left(Text;tCount – 1)
];
Case(
Text = “” and Header = “”; “”;
tCount = 0; Header;
IndexWord(newText ; NewHeader & Text)
)
)

This script recursively breaks down all the letters in a String so that   IndexString(“Noah Harris”; “”) becomes:

Noah Harris
Noah Harri
Noah Harr
Noah Har
Noah Ha
Noah H
Noah
Noah
Noa
No
N

Now, obviously this is only so helpful. If you want to be able to use this in a dynamic filtering kind of a way, you need to create some fields and relate them. Lets use a Person table as an example.

Naturally, you’d already have fields for the name_first and name_last and probably the two calc fields name_wholeFL  and name_wholeLF. For dynamic filtering, lets add two more: Person::g_search and Person::nameSearch. Now, g_search is just a blank global field, but nameSearch is a calc field equal to

IndexString(name_wholeFL; “”) & ¶ & IndexString(name_wholeLF; “”) 

This gives you the breakdown of the first and last names, along with being able to search “Noah H” or “Harris N” and still be able to find my record.

Below is the relationship for setting up the filter.

TriggerRefresh_relationsihp

So If you’re a on a Layout based in Person, with a portal looking at Person~DynamicFilter, by typing into the g_search field – in this case, the one above the portal – you’ll automatically filter the portal.

TriggerRefresh_portal        TriggerRefresh_portal_filtered

Now, if you’ve been following along so far, you may realize something is off with the last step. If you modify the relationship graph to look as follows:

TriggerRefresh_relationship2

you can put two portals, one from each branching Table Occurrence, on top of one another, and set hidden conditions on them.

The reason for two portals is so that when the search field is blank, it’ll allow you to display all the records in the table from Person_PER – this, of course, can be sorted however you like. I usually opt to sort based on name_last.

The hidden conditions are pretty simple, for the portal looking at ~DynamicFilter, just set the hidden condition to

Person::g_search = “”

and on the portal for Person_PER to

Person::g_search <> “”

If the portals are lined up and on top of one another, then it’ll look very flashy!
Feel free to ask questions!

-NCH