A work-around to implement a multiple choice lookup field in your AppGini apps

AppGini provides a multiple choice option list field, which is a handy way to allow users of your app to select multiple values from a list, for example to add tags/labels for a record. This is how it works:

Tags field above allows users to select multiple values from an auto-complete drop-down.

And this is how to configure it in AppGini:

Creating a multiple-choice option list in AppGini. To accommodate a potentially large number of items, the field data type should be Text or similar.

There is a problem with the above approach however. If you want to modify the list (to add new options, edit, or remove existing ones), you have to do this in AppGini, by opening the project file, editing the list of options, regenerating, and re-uploading your application files to the server.

The above is doable, but it’s a lot of pain. There is an easier method fortunately, which is to include a list of options in a file inside the hooks folder. If you create a file in hooks with the special name of {tablename}.{fieldname}.csv (where {tablename} is the name of the concerned table, and {fieldname} is the name of the multiple-choice field, for example products.tags.csv), your AppGini app would use the contents of that magic file to populate the drop-down list when users are filling in that field. It would simply override the list of options configured in AppGini.

Editing this special file is much easier than having to edit the list of options in AppGini, regenerate and re-upload the entire app. The file contents should be a list of options, separated by double semi-colons ;;, just like the format used for option lists in AppGini. For example:

widget;;new;;summer;;light colors;;dark colors;;girls;;boys

Still, editing that file requires accessing the server through FTP, shell, or some online file editor. It’s much easier than re-uploading the entire app, but not easy enough 😀 … Moreover, it still requires admin access to the server. Non-admin users won’t be able to edit that list on their own.

What if you could store the list in another table that can be easily configured to allow some users/groups to add, edit and delete entries? We’ll see how to do this now!

STEP 1: Add a new table to your project

Let’s create the tags table, which would allow users to add/edit product tags, which would show up in the multiple-choice Tags field in products table. It’s a very simple table with just 2 fields: id, and tag:

The tag field should be set as a varchar or similar data type. It will be used to dynamically populate tags drop-down field of the products table.

Next, generate the application, making sure to overwrite existing files.

STEP 2: Configure the hooks for updating the options list file

Let’s now add code so that whenever a use adds, edits or deletes records from the tags table we created in step 1, the options list file would automatically be updated. First, let’s define the update_tags_list() function that would retrieve tags from the table, and use them to populate the options list file. In the generated hooks/tags.php file, append this code block to the end of the file:

And invoke that function after inserting any records in the tags table by calling it in the tags_after_insert() hook:

Do the same in tags_after_update() to update the list if any tags are edited:

And in tags_after_delete() as well:

STEP 3: Upload the app files to your server

We’re ready now to upload the updated app to the server. Make sure to overwrite existing files. If you visit the app in your browser, the new tags table would automatically be created. You can confirm by visiting the tags table and adding a few records:

The tags table, populated with some tags. These will be automatically used to populate the drop-down options list of the Tags field of the products table.

STEP 4: Begin populating your multi-choice list by adding records to the table

Any records added to the tags table should now instantly update the Tags list in the products table:

The options listed when populating the Tags field are now dynamically populated from the tags table.

Your app users can now control the tags list through the tags table if you give them permission to edit it. And this is done instantly without editing the project in AppGini, or uploading any files, or manually editing any files.

Possible enhancements

The above solution has room for enhancement that I’ll leave to readers 🙂

Consider the scenario where you added the tag dark colors for example to a product, then decided later to edit that tag in the Tags table and renamed it to darks … This will not automatically update the tag for products that already have it. Same if you decide to delete a tag from the Tags table. If that tag is already assigned to a product, it will stay assigned to it despite being deleted from Tags.

To fix this, if needed, you should add code to the tags_before_update() hook to get the old value of the tag and perform a search and replace operation to the products.tags field.

Similarly, to handle deleting tags, you should add code to the tags_before_delete() hook to also search for that tag in products.tags and clear it.

Alright, maybe I’ll handle this in some future post if there is enough demand for it 🙂

I hope that helps 🙂 Feel free to post any feedback or questions below.

Published by Genedy

I'm the founder of BigProf Software. We're a tiny team of developers who create tools that make it easy (and affordable) for anyone to create connected business applications that work from any device with a browser.

10 replies on “A work-around to implement a multiple choice lookup field in your AppGini apps”

  1. I think this tutorial is really useful and for me it is another big step forward of the wonderful APPGINI tool !!!

    An interesting improvement would be to be able to insert more fields in the tags table and make that every tag inserted in a record of the products table can be clicked to open a popup with the corresponding record in the tag table in detail view mode, so that when choosing a tag you can see the details.
    I think for example to a table “teams of employees” that allows you to choose more employees from the table “workers” that contains the fields worker_name, hourly cost, contract duration, company … In the table teams_of_employees only the worker_name would appear but clicking on the name you could view all the data of that employee by opening in a pop_up the corresponding detailed view of the worker table.
    Thank you!

    1. That’s a very interesting “twist” 😀
      The only technical issue here is that we can only retrieve the tags text but not their IDs stored in the tags table … So, we can’t access the tag directly for editing, but rather open a window that searches for it and lists all the matches (which would be only 1 match in most cases) … We can then open that match for editing. Here is the code .. you should added it to hooks/tablename-dv.js (where tablename is the name of the table containing the options list field):

      Replace fieldname in the above code with the name of the options list field, and sourceTablename with the name of the table storing the tags.

      Glad you liked this post 🙂

  2. This is very good, but I’m having a problem with it.
    In my case, my csv file changes very often (it is dofferent for every record). The csv file updates correctly, but the application keeps looking at a (browser) cached version (all is ok, if I refresh the browser every time). I’ve tried using htaccess to stop this caching, but it does not work? Any ideas?

  3. UPDATE – Actually, I dont think the problem is caching – I think the csv file is being created after the HTML is created – could this be so, and is there a way of working around this?

    1. Hmm .. the CSV file is created/updated on submitting the form for adding new tags or editing existing ones. If you are doing this (editing/adding new tags) from the child record form, the drop-down won’t indeed be updated until the page is reloaded (in order to retrieve the modified CSV file from the server). It is possible to add code for a ‘refresh’ button beside the drop-down that would send an ajax request to the server to retrieve the updated CSV and re-populate the drop-down from the response.

  4. Hello there. Thanks for this improvement. I’m able to update the tags table and CSV file however when click save changes button, the selected options disappear from the field and aren’t saved to the server! How can I make this work ?

    1. Hmm … this needs to be inspected more closely. please press F12 to open the browser’s inspector, then click on the ‘Console’ tab. Is there any error reported there?

          1. Hello sir. The browser was caching all this time! It worked in another browser. Thanks a lot.

Comments are closed.