[size="3"]One of my ongoing problems with project after project in PHPRunner was needing to provide for the User of a Form Multiple Ways to Search for a Record in another table --
without leaving the Add Page of the Form.
And yet do this with Ajax lookups for a field combined with AutoFill fields.[/size]
Typically all my forms relied on AutoFill supplying values for as many fields as possible pulled out of another table.
This not only makes it easier to fill out a long form, but it is essential for making sure exactly the same standard data and table cross reference values pass from form to form, creating a data trail essential for pulling together Joined Reports.
In the Real World Users, however, are often missing a key id that would put the whole AutoFill convenience into motion.
For instance, an office worker receives a Fax from a business that neglects to put in their proprietary Business ID number.
The office worker using my form cannot simply type in this one ID to its field and get the record back and all the fields that would be populated with AutoFill values before completing the rest of the Form.
But this office worker might have a Business name or City and a Zip Code to help find the business that sent the fax.
Many times the business name would be a franchise, such as Walgreen's Pharmacy, with the same name in many cities so the name alone would need to be paired with the City and Zip.
But if I tried to set up the Zip Code field or the City name field ALSO as Ajax lookups there was a conflict.
I could not assign the Zipcode field or City fields separate Ajax lookups the same way I did for my master Business ID field.
Apparently it was "asking too much" to create an Ajax lookup in the same field that was also going to be AutoFilled by another field's Ajax lookup.
It created some instability and jittery javascript "weirdness" -- it didn't work very well.
Autofill Data was too often likely to disappear depending on the user's browser environment.
I FIXED this problem today by testing a hunch that I had. And I may be the ONLY PHPRunner user who did not know the answer to this problem until now!
Nevertheless, I will demonstrate my ignorance by admitting that I did not try this Search method until today.
- I went to my table query for this form and added some AS pseudo names for the fields that I also wanted to set up an Ajax Query list and AutoFill.
- I took my "zipcode" field, added .
- In my ADD page Editor I set City_Lookup and Zipcode_Lookup just under the "Business_ID" field.
- In the Query Wizard I used the same table for my "Lookup" fields as I did for my "Business_ID"
- I used Concat to add more visual information -- for zipcode lookup: concat(zipcode,' ',bizname,' ',city)
- for city lookup: concat(city,' ',zipcode,' ',bizname) and I did an "Order by" for the first field in each Concat().
- I set the AutoFill for the other fields in each of these, the same way I did for the "Business ID" field.
What I got works perfectly.
The office user can use ANY of the first 3 fields' Lookup Wizard AJAX popup lists.
Whichever of these first 3 fields in the form is used, [color="#8B0000"]Business_ID, Zipcode_Lookup, City_Lookup ... I get the AutoFill feature working for the rest of the form and there are no "jitters" or disappearing data before the form is submitted.
[size="3"]
A longstanding SEARCH problem solved using just Ajax list lookups in the Form itself instead of having to use a separate Query window.[/size]