Filter calculation for fields or value list entries containing a particular word

[Transitions]

I am trying to understand whether to use the filter function in a calculation. Here's the scenario:

I have one table called AllModules. This field contains four text fields: ModuleName, ModuleType, ModuleBody and ModuleID. The latter is concatenated from ModuleType and Module Name (ie, Module Name might be "Weekly" and ModuleType might be "Cleaning", so ModuleID would be "CleaningWeekly"). ModuleBody contains text that describes the ModuleName (ie, "This is a weekly cleaning module . . . ")

One a separate related table (related via ModuleID) I have a text field called CheckboxField that is set up as a checkbox. This field automatically reflects the value list of all the different ModuleType entries (ie, "Cleaning", "Mowing", etc). So if I add another ModuleType to the AllModules table (ie, "Waxing") it will automatically show up as an additional checkbox in CheckboxField.

In this second table, I've also set up another field that will allow - once a checkbox is checked - a way to choose which ModuleID to add to this second table. This is done as a drop-down menu using a value list from the ModuleID field, which means that every single ModuleID value (including all those that contain "Cleaning" or "Mowing" or "Waxing" show up in the pull-down list - such as "MowingWeekly", "MowingDaily", "CleaningWeekly", "CleaningDaily", "WaxingWeekly", etc).

I've been able set up a calculation using PatternCount to identify when an arbitrary box is checked in the checkbox, and I'd like to be able to filter the ModuleID options in the pull-down menu to only those ModuleIDs that contain the word that's been checked in the checkbox (assumes for now, of course, that only one has been checked) so that checking "Mowing" would then only allow "MowingWeekly" and "MowingDaily" instead of all the ones I listed above).

I tried to do it this way:

If ( IsEmpty ( CheckboxField ) ; "None" ;

If (PatternCount(CheckboxField; AllModules::ModuleType) ; Filter (AllModules::ModuleID; [need help here - thinking it should look for any ModuleID word containing the ModuleType that's been checked]) ; "None" ))

I can tell when a box is checked (since PatternCount returns a 1), and I assume this also allows the formula to know the word that corresponds to the checkbox (ie, "Cleaning").

What I'm then trying to figure out is how to filter to just the ModuleIDs that contain the word for the ModuleType that's been checked in CheckboxField.

Am I way off? Thanks!

[Ender]

Can you post a sample database with the fields/tables involved?

[Transitions]

Here's the example. Note that all the module options show up in the drop-down menu whenever any box in the checkbox is checked.

Just trying to figure out how to get those that contain the word that's been check in the checkbox to show up in the drop-down menu.

Thanks.

ExamplePost.fp7.zip

[Transitions]

Was the example database helpful in explaining the problem I'm facing?

[Mikhail Edoshin]

If I understand you correctly, you want your value list contents to change depending on what module type is selected. Here's a sample based on your file (with changed field and table names though).

ExamplePost.fp7.zip

[Transitions]

Thanks - maybe I was making my example too complex for one set of choices. The reason I was using the checkbox, though, was to add multiple module names.

Should I approach multiple module names (on your "Contract" table in the example) via the way you allowed multiple selections on the "Linked Fields" Sample 2 database (from your great blog) or should I go back to trying to use a filter calculation?

I'm attaching another example db here to show what I'm using the choices in the checkbox to generate (a calculation that takes text written in a module body and applies "merge expressions" to it to generate text that merges fields into standard text).

Thanks!

ExamplePost2.fp7.zip

[Transitions]

Can anyone help with this issue? Totally stumped!

[Lee Smith]

Hi Transitions, and welcome to the forum.

I thought that Mikhail had already helped you. I've looked your files and his, and it seemed that his did what you were asking.

Your description, and files seem to be asking about providing access through a Conditional Value List,

what does his example file don't do that you want or were expecting?

Lee

[comment]

Should I approach multiple module names (on your "Contract" table in the example) via the way you allowed multiple selections on the "Linked Fields" Sample 2 database (from your great blog) or should I go back to trying to use a filter calculation?

I would say neither. Because the real problem is your structure. What you are describing is very similar to a typical invoicing solution: you contracts are invoices, your modules are products. To add multiple products to an invoice, you need a third line-items table.

To add a module, you would first select the module's basic types (mowing/cleaning/etc.). Then use a conditional value list to select the exact module by frequency (daily/monthly/...).

[Transitions]

Hi, thanks for the reply. Mikhail changed it to a radio button (ie, only one could be selected at a time).

When I tried to use his example against my original request (to have a checkbox that would allow me to choose one or more than one checkbox and then filter or display only the modules that contained the word for each checkbox) his example didn't work, as it was based on showing only related records for one choice.

I then put together a second example file that I hoped would be a bit more explanatory (named ExamplePost2.fp7) which is in my response after his posting.

Hope that helps with the confusion. Thanks!

[Transitions]

Hi Comment,

Thanks for the reply. I'm trying to understand the difference between what Mikhail did in his example and what I'm trying to do. He was able to get a radio button choice to filter / display records that contained the word for the radio button choice.

I'm trying to do the same thing but using a checkbox, so that one or more of the boxes could be checked, allowing records that contain the one or more words from the checkbox to then be displayed as options for the user to make a single choice from. Don't think I need a table, as the user would only get to choose one module, even if they've checked multiple checkboxes, but I'm at least trying to limit the options they'd have to chose from to correspond to the checkboxes they'd chosen.

I've been able to set it up to only accept modules that contain the words that match the checkbox (ie, it rejects a choice if I choose something that doesn't have one of chosen checkbox words in it)

Trying to make it easier for the user to make a proper choice by limiting the choices they see, so as not to confuse them with too many that would be rejected (sort of like the episode of The Jeffersons where George is accidentally locked in the asylum and can never seem to choose the right pen from the guy who keeps asking him to buy one of the pens he has inside his overcoat. Anytime George picks one, the guy tells him he can't have the one he picked because it's not for sale. Would have been easier if George's choices had been limited to the ones that he COULD buy as he tried three or four times to choose one that wasn't available and ultimately gave up ;-).

Thanks.

[comment]

I am afraid we are discussing two separate issues at the same time:

If a contract is allowed to have multiple modules, you MUST have a third table. That is a structure issue.

How to SELECT a module is another issue - a user interface issue. But it cannot be solved until the structure issue is solved - because the wrong structure will not allow it to work.

Edited October 8, 2006 by Guest

[Transitions]

Hi Comment,

The contract is allowed to have only one module, but the choices can come from any of the modules that match the chosen checkbox(es) criteria.

Let me use another analogy what I'm trying to accomplish: a hypothetical "game of chance" database. This db has two tables "modules" and "choice" (in lieu of contract).

The "modules" table has "modules" which are essentially numbers and letters with the module type tacked on as a prefix. One "module" type might be called "1digit", one might be called "2digits" and one might be called "alphabet".

So, under the module type "alphabet", the modules would be "alphabet-a", "alphabet-b", etc through to "alphabet-z". Under the module type "1digit", the modules would be "1digit-0", "1digit-1" etc to "1digit-9". Same would be true for module type "2digits", with 90 possible modules starting at "2digits-10" and ending at "2digits-99".

On the "choice" table, I have a checkbox now that has three choices (the module types) "alphabet", "1digit" and "2digits".

What I'm trying to get to, as an end result, is this: if the user chooses "1digit" in the checkbox on the "choice" table, they should only have the choice of choosing "1digit-0", "1digit-1" etc to "1digit-9" in the "modules" pull-down box.

If they chose "1digit" and "alphabet" in the checkbox on the "choice" table, they should have the choice of choosing "1digit-0", "1digit-1" etc to "1digit-9" OR "alphabet-a", "alphabet-b", etc through to "alphabet-z" in the "modules" pull-down box, but can only choose one of the total 36 possible options. Therefore, it would be extremely confusing to have the "2digit" options (which adds another 90 options) showing up in the "modules" pull-down.

What would be the logic for someone wanting to do what I'm proposing? In the instance I'm suggesting here, it could be used to increase the odds for a raffle or some other type of game of chance. If the user feels "lucky" they could select all three checkboxes, then choose one of the 126 modules from the pull-down. On the opposite end of the spectrum, users feeling a bit less comfortable might decrease their odds by choosing only "1digit" which then only gives them one of 10 modules to choose from. Those who feel neither "lucky" or "unlucky" might choose "alphabet" and "1digit", giving them the choice of one of 36 modules (or 116 modules if they select checkboxes for "alphabet" and "2digits").

Does what I'm trying to do make sense now? I was thinking a filter calculation might allow only modules corresponding to those checkbox(es) that have been selected to be visible in the module pull-down menu, but am unsure how to make it do what I'm doing.

[comment]

The contract is allowed to have only one module, but the choices can come from any of the modules that match the chosen checkbox(es) criteria.

See if this helps.

ExamplePost.fp7.zip

[Transitions]

Hi Comment,

Thanks for the reply and example; yes, your example is the route I'm trying to go.

I noticed if I check off one of the module types (ie, "waxing") and the choose a moduleID with that name, then uncheck the same module type from the checkbox, that the ModuleID changes to a number and the Description retains the words of the previously chosen ModuleID's description. What I'd been able to do was force the ModuleID back to "None" and have it drop the description words. Am I stuck with an "either / or" scenario?

Also, quite curious to know how your duplicate table "Matching Modules" allows the filtering when mine wouldn't (ie, learning how to avoid the problem in the future). Thanks again for your hard work on understanding my problem!

[comment]

I noticed if I check off one of the module types (ie, "waxing") and the choose a moduleID with that name, then uncheck the same module type from the checkbox

I don't see why you would want to do that - unless it is to select another module of a different type. After you do that, the chosen name will be displayed again. There are many alternative solutions to this select/display problem. I chose what I think is the simplest one for your scenario.

Regarding your second question, you need to examine the relationship between Contracts and Matching Modules, and the definition of the value lists.

[Transitions]

On the second question, I see the relationship and understand the fact that the value list is displaying information from two fields in "Matching Modules", but can't see where the "Matching Modules" table is generated from (and why it's purple in the relationship) as "Define Database" tables only shows two tables: "Module" and "Contracts". Is there some black magic going on here that creates a third table without having it actually appear as a table under "Define Database"?

[comment]

MatchingModules is another occurence of the Modules table. They share the same color in the graph to emphasize that both are based on the same table.

[Transitions]

I've now read quite a bit on occurrences, and I understand "how" to create them, but curious to understand the "why".

If I drag a link between two tables, I know that creates a single-criteria relationship, and if I drag two links between two tables, it creates a multi-criteria relationship, which creates an AND situation. Is an occurrence link like the one on you created an OR rather than an AND or something completely different?

Thanks.

[comment]

Something completely different. We need TWO relationships between the two tables. One is for selecting only, the other is the actual link between the selection and the selected. You cannot have two SEPARATE relationships betwen the same two occurences.