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.
Post Reply
FlemmingDK
Posts: 2
Joined: 2014-03-05 09:43

Lookup field as Multiple-Choice

Post by FlemmingDK » 2014-03-05 09:55

I have a Support Case site where I have a main table (SUPPORT CASES) and another table with HARDWARE.

When I create a new Support Case, I have no problem in creating a Look Up field to the HARDWARE table, but can only select one option from the Drop Drow list. If I use an Options List I can check Multiple Choice.

Is it possible to do this with a Lookup Field from within AppGini, without additional change to the PHP Scripts? In my case, a SUPPORT CASE can easily be connected to several HARDWARE ID's or just one. But I prefer not to use the Options List as I can add comments to the HARDWARE records and add additional properties to this table.

Best Regards and thanks for a true gem in software :)

Flemming

User avatar
a.gneady
Site Admin
Posts: 1021
Joined: 2012-09-27 14:46
Contact:

Re: Lookup field as Multiple-Choice

Post by a.gneady » 2014-03-06 10:20

Unfortunately lookups don't yet support multiple choices ... though we'll try to implement it in future releases.
:idea: Learn all the tips and tricks of customizing AppGini apps through our online course.

:arrow: Mass Update plugin: Update multiple records at once and improve your workflow efficiency.

:arrow: Summary Reports plugin: Build powerful reports and charts for your AppGini apps.

:arrow: Search Page Maker: Compose user-friendly yet powerful search pages for your AppGini apps.

FlemmingDK
Posts: 2
Joined: 2014-03-05 09:43

Re: Lookup field as Multiple-Choice

Post by FlemmingDK » 2014-03-08 12:39

Thanks for the reply.

I am looking forward in seeing it implemented and will work around it for now by having multiple entries of the Support Cases. It is, after all, quite easy to open an existing case and save it as a new case :)

krieshen
Posts: 3
Joined: 2016-06-09 16:36

Re: Lookup field as Multiple-Choice

Post by krieshen » 2016-06-13 23:39

Hi, curious to know if there is a solution for the lookup field as multiple-choice already.

PaulKeates
Posts: 3
Joined: 2016-01-27 02:24

Re: Lookup field as Multiple-Choice

Post by PaulKeates » 2016-10-12 21:54

Hi,

I also need to do this for a project I'm working on. This would enable 'tagging' where you could create a table with as many 'tags' (terms) as needed and dynamically add them to another tables' records at-will. The ability to add more than on 'tag' would be super!

Is it possible to do with custom code? I would pay if reasonable costs.

Thanks,

Paul

chrisagon
Posts: 1
Joined: 2016-10-13 19:38

Re: Lookup field as Multiple-Choice

Post by chrisagon » 2016-10-13 20:23

Hi,
I confirm Lookup field as Multiple-Choice will be a super feature for appgini. I need too a multiple selection from a lookup table.
It seem to be possible but it is not because we need to have text in a primary key and it is not possible with MySQL.

tyroden
Posts: 1
Joined: 2017-01-05 16:10

Re: Lookup field as Multiple-Choice

Post by tyroden » 2017-01-05 16:11

I too could use this feature, in several projects / places. Anyone have a work around?

User avatar
baudwalker
Veteran Member
Posts: 112
Joined: 2015-02-03 08:08
Location: Bellingen NSW Australia

Re: Lookup field as Multiple-Choice

Post by baudwalker » 2018-04-26 05:46

me too

tminh
Posts: 22
Joined: 2019-05-23 02:26

Re: Lookup field as Multiple-Choice

Post by tminh » 2019-07-01 11:17

me too, please add this feature

onoehring
Veteran Member
Posts: 248
Joined: 2019-05-21 22:42
Contact:

Re: Lookup field as Multiple-Choice

Post by onoehring » 2019-07-01 13:31

Hi Ahmed,

lookup fields with the possibility to choose more than one option are pretty neat of course. I think it should be discussed (well thought over) how this is handled internally i.e. in the database, so I want to share my 2 cents.

If one would to that database wise, we would need a 1:n relation to another table, and that table holds all chosen IDs connected with the master ID from the master table. If this is handled differently, I am not so sure how people (here) want to handle this.
Putting all values into one field would probably mess up AG's functions to use the PK definition of the mastertable for the field to hold the FK - how would more than one FK be saved into such a field. Probably a textfield whould be able to hole more than one value, seperated by some identifier to the different values.

The layout would make it possible to use a html select statement with more then one line as well (user should be able to define a max lines).

I do see the sweetness of that honey pot, but I am not so sure, if "we" are aware what and of the number of problems that arise, when this kind of database entry has to be used in another database, being exported as CSV... I myself see a lot of unanswered and highly problematic questions concerning this suggestion.

As I am a "database"-creating person and would feel much happier by using a 1:n relation. AG could make it possible to choose more than one value and fill more than one record in the details table.

Olaf

fgazza
Veteran Member
Posts: 70
Joined: 2019-04-30 17:37

Re: Lookup field as Multiple-Choice

Post by fgazza » 2019-07-01 18:09

Hi everyone!

Have you seen the great blog article: "A work-around to implement a multiple choice lookup field in your AppGini apps" ?

https://bigprof.com/blog/appgini/a-work ... gini-apps/

I think it may be a good solution to manage multiple choice lookup field!

What do you think?

Regards.

Fabiano

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

Re: Lookup field as Multiple-Choice

Post by jsetzer » 2019-07-01 19:13

I'd like to share my experience with you and BigProf to bring forward this feature request. I've done different workarounds with hooks for multiselect relations as you can see in this article...

viewtopic.php?f=8&t=10&p=8956&hilit=multi#p8956

2018-10-17_20-56-51.gif
2018-10-17_20-56-51.gif (55.79 KiB) Viewed 272 times

... AND ...

...another solution, which is the version I'm currently using in my projects. Have a look at the following screencast:

2019-07-01_20-33-31.gif
2019-07-01_20-33-31.gif (167.81 KiB) Viewed 272 times

This is not only multi select options list but data from different tables. Evertything has been done with hooks. The assignments are being saved in a relationship-table on saving the record itself.

Although I already have a working solution for my projects and customers, I'd really like to have it in AppGini standard. So +1 for this feature request!

Ahmed's article mentioned before is a really good starting point. A widely used example is the bookstore-example. A book can have multiple authors, and an author can write multiple books. Next to "books" and "authors" tables...

books
id
name

authors
id
name

...you will need a third table "books_authors" for example.

books_authors
id
book_id
author_id

In details view of a book you need an input field showing the related authors and a dropdown list suggesting the non-related authors. On saving a book, the non-related authors have to be removed from books_authors, and new authors have to be inserted in books_authors-table.

In details view of an author you need an input field showing the related books and a dropdown list suggesting the non-related books. On saving the author, the non-related books have to be removed from books_authors, and new books have to be inserted in books_authors-table.

I know by my own experience that it will be quite a lot of work for BigProf to implement that into AppGini User Interface. Those inputs have to work in two directions, for example in author detail view you need assignment of books and in book detail view you need assignment of authors. BigProf will have to consider:
  • table-name for the relation-table (here:books_authors)
  • primary key of the relation-table (here: books_authors.id)
  • foreign key column name in the relation-table pointing to the left table (book_id or author_id)
  • foreign key column name in the relation-table pointing to the right table (author_id or book_id)
  • table-name for the left table (books or authors)
  • table-name for the right table (authors or books)
  • primary key of the left table (books.id or authors.id)
  • primary key of the right table (authors.id or books.id)
  • display column(s) for the left table (I only support one column in my solution)
  • display column(s) for the right table (I only support one column in my solution)
  • filter criteria to show only certain items of the left table in the dropdown (I did not implement this in my solution)
  • filter criteria to show only certain items of the right table in the dropdown (I did not implement this in my solution)
  • helptext for the field in DV of left table
  • helptext for the field in DV of right table
Additionally, if your relation-table has columns like "created_by" or "created_on", they will have to be populated, too, as soon as you have added a relation.

Putting it altogether, it's a lot of work and scenarios to consider. It would push AppGini forward, sure, but only if it works 100%. To be honest, this is a hard task having it in standard fulfulling many of our wishes!

In my implementation there is only little code in my TABLENAME.php hooks file...

Code: Select all

// for setup of a relationship
function wirtschaftseinheit_flurstuecke()
{
    $multiselect = new \bizzworxx\ManyToManyRelation('wirtschaftseinheit_flurstuecke', 'id', 'wirtschaftseinheit_id', 'Flurstücke');
    $multiselect->setRight('flurstueck_id', 'flurstuecke', 'id', 'nummer');
    $multiselect->createdBy = "createdby";
    $multiselect->createdOn = "createdon";

    return $multiselect;
}

// for rendering the input field and dropdown in DV
function wirtschaftseinheiten_dv($selectedID, $memberInfo, &$html, &$args)
{
    $flurstuecke = wirtschaftseinheit_flurstuecke();
    $html .= $flurstuecke->render($selectedID);
}

// and for update...
function wirtschaftseinheiten_before_update(&$data, $memberInfo, &$args)
{
    $flurstuecke = wirtschaftseinheit_flurstuecke();
    return $flurstuecke->update($data, $memberInfo, $args);
}

...but you can imagine there is a lot of PHP-code and JavaScript/JQuery (TypeScript in my case) behind the scene.

I hope my explanations will help pushing this requirement into AppGini 6 or so.

Kind Regards,
Jan

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

Re: Lookup field as Multiple-Choice

Post by jsetzer » 2019-07-01 19:54

Oh, I forgot to mention:

Users expect that they can click on one of the related items and will be redirected to that record (and vice versa):

ezgif.com-optimize (1).gif
ezgif.com-optimize (1).gif (248.1 KiB) Viewed 267 times

On book-DV they expect they can navigate to the related author(s), and on author-DV they expect they can navigate to the related book(s).

This may become problematic:
If the current record has been modified ("is dirty") and they navigate to a different record, the changes on the current record may get lost - unless you deny navigating to another record (or open it embedded or in a new tab which both I dislike).

This means as soon as the record gets modified, the click-handlers of the relation-buttons have to be disabled or removed.

Regards,
Jan

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

Re: Lookup field as Multiple-Choice

Post by jsetzer » 2019-07-02 06:21

One more thing: Ownership of records
  • After creation of a new relation (insert into relation-table) we have to set the owner.
  • After removing a relation (in relation-table), we have to delete the ownership-record to avoid orphans.
Regards,
Jan

onoehring
Veteran Member
Posts: 248
Joined: 2019-05-21 22:42
Contact:

Re: Lookup field as Multiple-Choice

Post by onoehring » 2019-07-02 06:43

Hi Jan,
thank you for this deep input.
Hopefully it will be helpful.

Olaf

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

Re: Lookup field as Multiple-Choice

Post by jsetzer » 2019-07-04 07:57

Another thing to consider:

On "Save as copy" of a record, I'm wondering if the relations should also be copied?!

The relations between books and authors are stored in a separate table books_authors for example. Duplicating a book-record or duplicating an author-record will not duplicate all relations automatically.

From my experience there are scenarios in which duplicating relations is important and other scenarios in which you don't want those duplicates. So in case BigProf is going to implement this, we'd need behaviour-options in AppGini for "save as copy", or we need a hook-function being called before and/or after "save as copy" where we can duplicate relations using SQL by ourselves.

Regards,
Jan

Post Reply