Yet Another Multi-Select Value List Problem

[mdevane]

Be gentle -- a Newbie looking for help from the many experts on this forum on the following:

Requirement: I need to mimic existing field layout of an existing web-based solution to provide for future "import" capability to that system. There are ~140-150 fields in the existing solution, 40-50 of which are "multi-value pick" lists, most of which have static values. These value list fields allow for 2-20 picks per field depending on the field in question. Total option counts are typically 5-20 options from which the user can select. The web-based solution returns the selected items from pop-up window in the form of comma-delimited lists of value-ID's. If you click on the field after the initial picks are made, this will bring up the pop-up window again, with selected items "check-boxed". Changes can be made to the selections and when complete will re-generate the comma-delimited list. This is important since when you export records to your local system, the tab-delimited records will export the multi-select fields with the comma-delimited selections intact.

Through much research on many FM forums, I have created a solution which does provide for pop-up window with the ability to select and un-select multiple values in the check-box set. Unfortunately, due to lack of experience, I have not been able to figure out how to concatenate the resulting value list and return that result set to the field in format required. Although I have been in the enterprise software field for 30+ years, it has been that long since I programmed, so my current skills are *extremely* rusty, to say the least. Any help would be greatly appreciated -- this is for a system I am creating to help my wife in her real-estate practice. I started this project a year ago, but set it aside when I became too busy at work.

I have attached a sample of the pick-list and associated scripting I have cobbled together so far. By clicking on the AttributeIDValueList field, the selection window will pop-up and selections can be made, but I do have have the requisite skills yet in FM to generate the result set and capture it in the AttributeIDValueList field.

Thanks again for your help. If I have gone down the wrong path so far, please tell me -- no feelings will be hurt

Regards, Mark

[Ocean West]

I use this custom function to choose the items from a utility type list of values ( a list view or portal of values)

https://github.com/petrowsky/fmpstandards/blob/master/Functions/ValueToggle.fmfn

the item I store is the ID of the value, you can write that to a field or into a $$variable.

If you need it to be comma separated then just a Substitute ( KEYFIELD ; "¶" ; "," )

[mdevane]

I use this custom function to choose the items from a utility type list of values ( a list view or portal of values)

https://github.com/petrowsky/fmpstandards/blob/master/Functions/ValueToggle.fmfn

the item I store is the ID of the value, you can write that to a field or into a $$variable.

If you need it to be comma separated then just a Substitute ( KEYFIELD ; "¶" ; "," )

Stephen - Thanks much for responding so quickly. I looked at your custom function and although I do not know FM that well yet, I understand (or think I do) what it does. My challenge is that due to my lack of understanding of FM, I am not sure how to incorporate your custom function into the sample file/script that I provided. I have added your Custom Function to my file, and I assume it should be incorporated into the existing Toggle Attribute script that I currently use, but not sure how to do that.

Also, am not sure about your last line above -- I would assume if I use this function on a regular basis, it would be easiest to substitute all occurrences of "¶" with "," as the field delimiter in the function, but I read your comment to be something different - am I correct? sorry for being so dense...

Do you happen to have an example I can review?

Thanks again -- a step closer each day!

[bcooney]

In FM, you'll need two fields, one for the return-delimited key field and one for the substituted, comma-separated corresponding field (an unstored calc field).

[comment]

I have attached a sample

Try again.

[mdevane]

Try again.

It would help if I actually added the file.

Scroll Checkbox Multi-Select Value List.fp7.zip

[comment]

The file looks somewhat familiar... but I can't figure out what your question is.

[mdevane]

The file looks somewhat familiar... but I can't figure out what your question is.

It's probably partially yours! I started looking at possible solutions over a year ago when I had some free time. Just starting to work on it again.

My question is how to incorporate Ocean West's custom function (above) into the script I uploaded. I need to use "pop-up" pick lists of related tables and need to store the resulting pick-list as a comma delimited set. This is a result of a) existing web-based solution we will be integrating with in the next year, and screen real-estate -- 40-50 of these portals will take up too much room on screen, even using a tab approach. See my initial commentary above.

Hope this makes sense to you.

[comment]

If you are using a pop-up window to select values, showing a list layout in the pop-up will make things simpler than using a portal. I am not sure why you want to store the selected values as comma-delimited instead of using Filemaker's native return-separated list. In any case, it's an easy conversion - just substitute the return with a comma.

Having 140-150 fields to describe an object raises a question whether your data model is correct.

[Ocean West]

here is a modified version of your sample file.

Basically I added a few custom functions, that will produce the results that I think you are after.

In addition I took the liberty to add a "group" to your attribute list so that you could either filter and sort your attribute list,

this way it will give you more flexibility.

Scroll Checkbox Multi-Select Value List.fp7.zip

[mdevane]

Sorry for the delay in responding -- have been traveling. Your mods do the trick! Haven't had a lot of time to play with it, but was able to quickly adjust to my scheme. A few *novice* questions:

1) I assumed (wrongly) that the table relationships would be "many to many" - each object can have many assigned attributes, and each attribute can be assigned to many objects. How were you able to avoid a join table? why did you take this approach?

2) Is your answer to #1 why you did not use a portal to display the pick list?

3) will there be an imposed penalty for not using my original approach? e.g. - reporting on # of objects with X attribute or exporting object files and their associated attribute relationships?

4) for this solution, in most cases the "group" functionality will not be needed as the # of selection options is relatively small (<6) -- is there a simple way to eliminate groups on pick lists?

Thanks again for your help.

[comment]

I assumed (wrongly) that the table relationships would be "many to many"

No, your assumption was correct. Each object can have many attributes, and each attribute can be assigned to many objects. Filemaker allows a many-to-many relationship without a join table by storing a return-separated list of related IDs (e.g. a checkbox field) in the matchfield. However, there are limitations to this approach: you cannot have any join-specific attributes (such as the date of join) and some types of reports are not possible.

The example file you have attached uses this model, and naturally we have assumed (at least I have) that this is what you want.