2 Replies Latest reply on Mar 29, 2015 4:07 PM by LD

    Value lists and indexing global fields



      Value lists and indexing global fields


      I have an sql function that triggers via onobjectenter/exit to populate a global field (Valuelist_Source) with a return separated list of values. There is then a value list based on this global field. This value list is used on about 20 different fields to display conditional data from another table. It is based on the Virtual Value List demo which allows you to base a value list on an unstored calculation - although my implementation doesn't use any custom functions, so maybe I am missing something from the technique in the demo. Regardless, it works well, and the issue I have is possibly not related to anything I just described.

      I would like to 'Auto-Complete using value list' for these dynamic lists. However that function requires that the value list be based on an indexed (i.e. non-global) field. The problem is if I change the Valuelist_Source field that this dynamic value list is based on to an indexed field, the resulting value list displays all the conditional data currently being used in lists by all clients, whereas when the Valuelist_Source field is a global field it displays a list unique to each user (since global fields are stored locally). So indexing the field means that if two or three people are using the database at once, their conditional lists show a combination of the data that all of them are accessing. (E.g. one user is looking at a list of Cities, one is looking at a list of Zip Codes, each of them then see a mixed list of Cities and Zip Codes.)

      I apologize if I haven't explained anything clearly. My final question then is this: is there any way to 'Auto-complete using value list' for a non-indexed field, or is there any way to store an indexed field locally for each client, rather than on the server?

        • 1. Re: Value lists and indexing global fields

          I think there are some details missing here in your description. Normally, use values from field value lists draw their values from indexed fields. It is indeed possible to draw values from an unindexed field if at least one of the two fields in a "use values from field" value list IS indexed and is the "sorted" field of the two. But in such cases, the data from each unindexed field becomes a single value in the list even when you have return separated values in the field. For a global field, this produces a value list of a single value.

          Are you sure that your global field is not serving as a match field in a relationship to filter the values of your value list?

          • 2. Re: Value lists and indexing global fields

            Hi Phil,

            Thank you for the response, I apologize if my explanation is unclear, it's been a while since I set it up.

            The value list is based off of the relationship in the picture. The actual fields don't matter, and the relationship doesn't matter, but somehow the relationship enables filemaker to use an unindexed field for a value list. I'm not sure if I am allowed to post external links, but a more in-depth implementation of the technique and a demo are located here: http://filemakerhacks.com/2012/07/25/magic-value-lists/

            The ExecuteSQL function to populate the list is also quite simple, after determining the context for the valuelist I use Set Field ( ExecuteSQL ( "select Datapoint FROM DataTable WHERE FieldID = ? and ForeignKey = ?" ; "" ; "" ; $$CurrentFieldID ; $$CurrentForeignKey )) to set the global field, commit records, then re-enter the field and viola, the valuelist populates correctly.

            So at the end of the day, as odd as it sounds, it works. My only issue is in the indexing of this Valuelist_Source field, and getting Auto-complete to work with the resulting value list.