Lookup field as Multiple-Choice

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup field as Multiple-Choice

Post by jsetzer » 2020-02-22 13:07

Warning (2)

It's the same with deleting master-records.

Deleting a master may have an impact on detail records even if the deleted master has not been referenced by any detail-record at all.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup field as Multiple-Choice

Post by jsetzer » 2020-02-22 13:30

Warning (3)

The multiselected values are being stored as a (=one) comma separated string in details table like "Peter, Paul". It it not normalized.
If you have master records like this, there will be more problems:

Example

master-table
  • id=1, name="Gabriel, Peter"
  • id=2, name="McCartney, Paul"
  • id=3, name="Peter"
  • id=4, name="Gabriel"
Now imagine you have two details-records:
  1. In the first you select master #1 ("Gabriel, Peter")
  2. In the second you multi-select master #4 ("Gabriel") and master #3 ("Peter")
Expectation

You would expect the following user interface:
  • Record #1, values = [x Gabriel, Peter]
  • Record #2, values = [x Gabriel] [x Peter]
Unfortunately, the values being stored into the database for single-selecting #1 and for multi-selecting #4 and #3 would be identical. Both would result in the same details-record:

details-table
  • id=1, values="Gabriel, Peter"
  • id=2, values="Gabriel, Peter"
I have just double checked this behaviour in a test-application.

So this would give the identical presentation and selection-options for both details-records:
  • Record #1, values = [x Gabriel] [x Peter]
  • Record #2, values = [x Gabriel] [x Peter]
Obviously, this is not what we have expected.

Also the search&replace- and the delete-algorithm would bring unexpected results.

Conclusion
The recommended solution may fail if lookup values from master-table contain commas.

So once again: CAUTION when using this workaround :!:
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Lookup field as Multiple-Choice

Post by jsetzer » 2020-02-22 13:55

Summary (AppGini 5.8x):

From my personal point of view and experience, as of February 2020:
  1. Multiselect lookups with an easy-to-use user-interface are very, very complicated to implement
  2. There is a workaround using multiselect options with dynamic storage of CSV-files
    • This workaround cannot handle references (primary keys) and therefore it cannot promise correctness
    • There is no bullet-proof solution for handling changes
    • There is no bullet-proof solution for deleting values nor cascading delete
    • It handles string representations and there are several problems due to non-normalized storage
    • It can be used if the lookup values are just plain single words.
    • It should not be used if the lookup values may have duplicate names
    • It should not be used if the lookup values may have commas inside
  3. From my point of view, the only secure and bullet-proof way has been described by Ahmed here:
    https://bigprof.com/blog/appgini/how-to ... n-appgini/
    • positive: Built-in solution, fully integrated, configurable, lookups can be referenced by primary key, supports permissions (!)
    • negative: It is much more complicated for users to understand and use this
I think this is a very important feature missing. I wish there was a better solution, a more comfortable solution for users, but I don't see any right now, which is (a) easy-to-use (b) secure and (c) bullet-proof.

It would be great if others could contribute with their knowledge and experience.

Kind regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

sathukorala
AppGini Super Hero
AppGini Super Hero
Posts: 121
Joined: 2020-02-16 16:29

Re: Lookup field as Multiple-Choice

Post by sathukorala » 2020-02-23 02:38

jsetzer wrote:
2020-02-22 13:05
Warning (1)
A word or warning to those who are going to follow that recommended soluton:

Example

master-table
  • id=1, name="Peter"
  • id=2, name="Paul"
  • id=3, name="Peter"
Now let's create a new details-record and multiselect "Paul" (id=2) and "Peter" (id=3).

details-table
  • id=1, values="Peter, Paul"
Now let's change record #1 of our master table.
Change record #1 and rename "Peter" to "Alexander"

master-table (after changing #1)

  • id=1, name="Alexander"
  • id=2, name="Paul"
  • id=3, name="Peter"
Now automatically execute a search & replace function after update of master-table.
  • Find all records having "Peter" in values field
  • replace "Peter" by "Alexander" in values field
Expectation

We expect that our details-record will NOT be touched, because we have selected #2 and #3 but we have renamed #1. This should not have an impact on our details record.

But what does search&replace do
  1. It will find "Peter" in our details-table
  2. It will rename "Peter" by "Alexander". So the final result will be:
details-table (after search&replace)
  • id=1, values="Alexander, Paul"
Obviously, this is wrong, because in details-table we had multiselected records #2 and #3. Then in master-table we have renamed record #1, but NOT record #3. So details-record #1 does not contain any reference to the renamed master-record #1. But the search&replace replaced it anyway. Which means: search & replace has failed.

The reason is, that the id's are not being stored when using the multiselect option which is available in current version of AppGini.

Conclusion:
Be careful with this solution if your app is more than just a playgound!

Thank you for your feedback. We're quite educated about pros and cons
Can you at least help me please with a 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.
and
to handle deleting tags, a code to the tags_before_delete() hook to also search for that tag in products.tags and clear it. ?

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Lookup field as Multiple-Choice

Post by onoehring » 2020-02-27 09:57

Hi,

just a quick reply. I think much has been said (by Jan) already and I just wanted to agree with him, that
From my point of view, the only secure and bullet-proof way has been described by Ahmed here:
https://bigprof.com/blog/appgini/how-to ... n-appgini/
positive: Built-in solution, fully integrated, configurable, lookups can be referenced by primary key, supports permissions (!)
negative: It is much more complicated for users to understand and use this
seems to me the best solution.

Best in terms of data(base) correctness, but maybe not best in terms of layout (and, for some, ease of use). Nowadays in my experience people seem to prefer nice looking applications even when this means contradicting your data. So, yes, it would be nice to have another layout option of M:N relations in AppGini (instead of adding detailrecords to the bottom of pages).
I would also agree with Jan (or even go one step further) that considering the other blog post of Ahmed where he describes a not-so-save-solution (as detailed by Jan above) should not be used. Especially not by people starting with database applications when it is/might be unclear what primary keys and constraints are for.

Olaf

Post Reply