Create option list with ID

[saymaad]

Hi,

I have a question about option lists, for example:
Monday;;Tuesday;;Wednesday;;Thursday;;Friday;;Saturday;;Sunday

How can the dropdown show these days while the values stored are just abbreviated or ID numbers assigned to these days, such as:
1,Monday;;2,Tuesday;;3,Wednesday...............

OR

Mo,Moday;;Tu,Tuesday;;Wd,Wednesday...........

Can appgini produce option lists like dropdowns where the stored value is the ID and Displayed value is a description?

[pbottcher]

Hi,

actually what you are looking for is the lookup that stores the id of the lookup record, but displays the description, or whatever you want.

[saymaad]

Thanks for the response pbottcher,

Indeed it is lookup behaviour that I seek, but from option list in AppGini.

The problem I have at hand is that I do not wish to create tables for gender, days of week, cities or product types. The reason being these are constant which will not change, so why have them stored as table and overburden the application with so many tables and CRUD files.

While AppGini does have the feature to create option lists, they come with a drawback of not having an ID associated. Why I need ID? for export and reporting purposes these IDs are already set on other db and local files.

Hope I am making sense here. I am looking to extend the option list of AppGini by hooks or any other means to include IDs and behave similar to lookup fields i.e. show pre-defined description and store related ID.

best,
Saymaad

[jsetzer]

I fully support pbottcher's answer: As there is no optionlists like A:ABC;D:DEF;KEY:VALUE, lookups are the built-in way to handle such (or build your own select-controls with hooks, which I sometimes do).

[...] these are constant [...]

Believe me: From my 30 years of software development experience, lists you consider being constant, may change in the future. You mentioned "gender"-list. Thats a good example, which, at least in Germany and many other contries, did change, recently. Also cities do rename, product types may change etc.. Or imagine you have to provide the app in a different language. There are many reasons why Lookup-Tables are superior to constant (hard-coded) lists, even if setup takes seconds longer. Database is a good place for storing data.

Tipp
For pseudo "constant" lists I use to "seed" my lookup tables automatically, which means, in one of my scripts I use PHP and SQL for inserting several initial records, if they don't exists, yet. You may use unique constraints, for example, to ensure uniqueness.

Whishlist
I wish BigProf would add a hook for dropdown lists. I have suggested this as feature-request a few weeks ago. This would allow us to return any given key-value-list. I hope this will come some day.

[saymaad]

jsetzer thanks for the detailed answer, my application currently has 44 tables and out of them 30 are just to serve as lookup fields. On the forum I have seen a few people complain about performance issues beyond 90 table mark, that was one of the major reasons I am thinking for having hardcoded lists or lists which are fed by xml / hooks / etc.

Similar to these 14 transactional forms, my goal is to add 70+ forms over the next two years as we plan to be paperless in that time. However, with the stats, I may end up around 300 table mark. From your experience would that be too much for this platform? Have you faced any performance issues with AppGini with larger number of tables? Any other tips before I scale up?

The development from my end on this project has been slow this past year as we keep waiting for library and framework updates like Bootstrap, select2, etc. anticipating breaking changes.

[jsetzer]

AppGini performance with >300 tables is a completely different story. For performance issues you may consider opening a different forum thread. And always keep an eye on normalization. Also, take database-indexes into consideration which may help a lot in performance.

Talking about normalization

From my experience, many lookup tables have identical or very close schema like this:

contract_states

• id
• name
• position
• description
• icon
• is_enabled

And then a lookup looks like contracts.state_id --lookup--> contract_states.id.

So, if I expect only a few lookups, I usually copy that table, let's say contract_states, document_states, task_states, then configure each lookup-field.


But if I expect more, I usually make it more generic for less redundancy and better normalization:

More generic table states:

• id
• type_id
• name
• ...

As you may have seen there is an additional column type_id.

Now, I only need this one states table. In each lookup (from contracts, documents, tasks etc.) instead of referring to contract_states, document_states, task_states etc. I can now refer to the same table states like contracts.state_id --lookup--> states.id and I only have to change the query (via Advanced Query option) to ...WHERE type_id=1, ...WHERE type_id=2, ...WHERE type_id=3 etc.. Or, if you like, use a varchar field type_code like "CONTRACT", "DOCUMENT", "TASK" or even an (one!) additional statetypes-table and dropdown from states.type_id --lookup--> statetypes.id.

Summary

• Pure "category-like" lookup-tables can be reduced to a minimum by abstraction.