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.
Lookup field as Multiple-Choice
Re: Lookup field as Multiple-Choice
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lookup field as Multiple-Choice
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
You would expect the following user interface:
details-table
So this would give the identical presentation and selection-options for both details-records:
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
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"
- In the first you select master #1 ("Gabriel, Peter")
- In the second you multi-select master #4 ("Gabriel") and master #3 ("Peter")
You would expect the following user interface:
- Record #1, values = [x Gabriel, Peter]
- Record #2, values = [x Gabriel] [x Peter]
details-table
- id=1, values="Gabriel, Peter"
- id=2, values="Gabriel, Peter"
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]
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: Lookup field as Multiple-Choice
Summary (AppGini 5.8x):
From my personal point of view and experience, as of February 2020:
It would be great if others could contribute with their knowledge and experience.
Kind regards,
Jan
From my personal point of view and experience, as of February 2020:
- Multiselect lookups with an easy-to-use user-interface are very, very complicated to implement
- 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
- 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
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
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
-
- AppGini Super Hero
- Posts: 121
- Joined: 2020-02-16 16:29
Re: Lookup field as Multiple-Choice
jsetzer wrote: ↑2020-02-22 13:05Warning (1)
A word or warning to those who are going to follow that recommended soluton:
Example
master-table
Now let's create a new details-record and multiselect "Paul" (id=2) and "Peter" (id=3).
- id=1, name="Peter"
- id=2, name="Paul"
- id=3, name="Peter"
details-tableNow let's change record #1 of our master table.
- id=1, values="Peter, Paul"
Change record #1 and rename "Peter" to "Alexander"
master-table (after changing #1)
Now automatically execute a search & replace function after update of master-table.
- id=1, name="Alexander"
- id=2, name="Paul"
- id=3, name="Peter"
Expectation
- Find all records having "Peter" in values field
- replace "Peter" by "Alexander" in values field
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
details-table (after search&replace)
- It will find "Peter" in our details-table
- It will rename "Peter" by "Alexander". So the final result will be:
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.
- id=1, values="Alexander, Paul"
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. ?
Re: Lookup field as Multiple-Choice
Hi,
just a quick reply. I think much has been said (by Jan) already and I just wanted to agree with him, that
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
just a quick reply. I think much has been said (by Jan) already and I just wanted to agree with him, that
seems to me the best solution.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
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
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view