8 Replies Latest reply on May 22, 2015 10:11 AM by MorkAfur

    ZipCode Lookup Not Working As Expected/Wanted

    MorkAfur

      Title

      ZipCode Lookup Not Working As Expected/Wanted

      Post

      I’m trying to pre-populate the zipcode field using the possible values from the ZIPCODES table.

      Yet, FileMaker refuses to go along saying that I can only use a global field for unrelated fields.

      Huh?

      This is just a lookup table, NOT a related table. I’m just trying to set a variable in the script dialog. I don’t want to (nor should I) relate the customer’s zip code to the ZIPCODES lookup table.

      Here’s the exceedingly simple SQL:

      ExecuteSQL ( "select zipcode, city, state from ZIPCODES where state = ? and UPPER(City) = ?" ;""; ""; ZIPCODES::State; Upper(ZIPCODES::City) )

      Clearly, I don’t want to use a global field for customer zip codes.

      Why won’t filemaker let me execute this SQL?

      The query works fine from the JDBC client I always use to work with SQL in FM. (Example that proceeds 85 zip codes: “select zipcode, city, state from ZIPCODES where state = 'CA' and UPPER(city)= 'SAN DIEGO’”).

      Thanks in advance for suggestions.

      - m

      error.jpg

        • 1. Re: ZipCode Lookup Not Working As Expected/Wanted
          philmodjunk

          It's not the SQL query, it's the optional parameter values that you have set up that have the problem due to the context of your query.

           ; ZIPCODES::State; Upper(ZIPCODES::City )

          Are referring to fields that, from the context of your FileMaker calculation are coming from an unrelated table and thus trip this error message as no value can then be passed to that query to take the place of the ?

          What I see in your example actually does not make sense.

          As written, it is trying to use values from the ZipCodes table to look up values from the zipcodes table. Normally, you would be specifying a city and a state in fields from some other table (and possibly with a pair of global fields), in order to look up a set of zipcodes for that city/state combination.

          Note also that since a given city will have multiple zipcodes, it would seem that you'd also need something specified as a record separator in order to work with the results returned.

          • 2. Re: ZipCode Lookup Not Working As Expected/Wanted
            MorkAfur

            Yeah, I figured this part out but didn't update my posting in time. Sorry 'bout that. :)

            But, I have another strange problem.

            I added a second (non global) field called Possible Zips. The idea is to give the user a pop-up menu of zips to pick from when the city + state don't match any of the returned zip codes from the SQL.

            What I did was to create a pop-up menu with the SQL results for the city + state and then, in script, do a SET FIELD to update that Possible Zips field.

            All this seems to work fine in the debugger.

            Yet....In the layout, the expected values (For this city + state) are there, but also in the pop-up menu, I see the values from the last SQL (but they're not "checked").

            I'm using a Set Field to write in the Possible Zips with the values from the SQL results.

            The SQL is going into a global variable (so I can show/hide the "possible zips" control/text if zip is OK), but I clear out the Global Variable explicitly and even do a commit after that initialization.

            Really strange. I'm missing something here.

            I'm not sure why SET FIELD isn't overwriting the Possible Zips field with the values I see in the debugger. I'm doing an additional commit after this SET FIELD.

            Thanks in advance,

            - m

            • 3. Re: ZipCode Lookup Not Working As Expected/Wanted
              philmodjunk

              What I have already noted in your original query example is that you have not separated the values returned by the query with returns (Pilcrow operator).

              In a check box format, each value should be separated by a return.  So you may have a list of zipcodes all concatenated together as a continuous string of digits instead of return separated values and that would keep any check boxes from showing as selected. (This would also keep a value list in a popup menu from showing a check box by each value.)

              • 4. Re: ZipCode Lookup Not Working As Expected/Wanted
                MorkAfur

                I changed the SQL to this...

                ExecuteSQL ( "select zipcode from ZIPCODES where state = ? and UPPER(City) = ?" ;""; "¶"; $state; Upper($city) )

                This creates the same list of zip codes, but no difference in this strange display problem.

                Even though I explicitly do a SET FIELD for the pop-up field  (Possible Zips), it still has values from before. The debugger for the global variable $$validZips always shows the correct ones.

                Yet, if I type "12345" in the zip code field and TAB, then the Possible Zips will appear (HiDE WHEN) and its drop down has the correct zip codes for the current city + state, but also left over values.

                I've tried COMMIT and REFRESH OBJECT.

                Confused...

                It'll be a cool feature once it's working...

                Suggestions?

                - m

                • 5. Re: ZipCode Lookup Not Working As Expected/Wanted
                  philmodjunk

                  You'll need to describe the details of how you set this up. I can't quite figure out your set up from what you have described thus far. On the one hand, you are setting a field to the results of your query. On the other, you have some kind of value list set up. I can imagine a couple of possible scenarios here and am not sure which you have attempted.

                  It might be as simple as needing to commit records after setting the field...

                  • 6. Re: ZipCode Lookup Not Working As Expected/Wanted
                    MorkAfur

                    Understood. It's a bit confusing potentially. But, I think my problem is how I’m using the pop-up menu / value list.

                    So, on the layout there is a zip field where the client would enter the zip code.

                    Then when he tabs off the zip code field the script then does the SQL to a $$ValidZips variable. This variable always shows the right values. The script then populates the OtherZips field (the pop-up menu) with those values using SET FIELD...COMMIT.  (I've even put a SHOW DIALOG here, and it's the right values only.) 

                    The OtherZips field is defined as a pop-up menu/value list whose values are based on the OtherZips field. Now, maybe it’s including all values from that field for all records? Seems to be what's happening.

                    If the value list is including all values for that field, which does seem to be the problem, then I need a way to give the user a pop-up menu with just the zips from the global variable (SQL output).

                    Not sure how to do this. Tried to use the $$ValidZips variable as input to the value list, but that didn’t seem to work.

                    Tried to make the field drop down (Possible Zips) Global, but then I get an “Index Missing” error on drop down.

                    What’s the magic needed here?

                    Thanks Phil.

                    - m

                    • 7. Re: ZipCode Lookup Not Working As Expected/Wanted
                      philmodjunk

                      The OtherZips field is defined as a pop-up menu/value list whose values are based on the OtherZips field. Now, maybe it’s including all values from that field for all records? Seems to be what's happening.

                      That's it exactly. A "use value from field" value list will build a list of unique values found in the field over all the values in the table. A lesser known fact is that when you put a list of return separated values into such a field (or generate them with a calculation as I sometimes do to add a few default or "action" values to a list), each value appears separately in the list.

                      To get just the values from the current record, use a basic conditional value list setup. Set up a self join such as:

                      YourTable::PrimaryKey = YourTable2::PrimaryKey

                      Where your layout is based on YourTable. Set up the value list to list values from OtherZips, but from YourTable2 and then select the "include only related values starting from YourTable" option to limit the list to only values from your table.

                      I've done it this way before using ExecuteSQL and also by performing a find on records in another table and using the "list of" summary field to get and return a list of values that I then set a text field to in order to get my value list up and working.

                      • 8. Re: ZipCode Lookup Not Working As Expected/Wanted
                        MorkAfur

                        PERFECT!!!!!!!!!!!!!!!!!!!!!!!

                        You d'Man!

                        I should have thought of this....

                        Thanks Phil!

                        :)

                        - m