Build 1:1 relation with drop down... impossible?

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-05 10:07

Hi,

I have a table Container and a table ContainerDetails.
The ContainerDetails contains more information for each of the entries in the Container table.
I need to create a 1:1 relation between those two and tried to do this with a dropdown in the ContainerDetails table/form. AppGini can not create the code anymore throwing this error:
Error checking:
Field 'ecomo_ContainerSpezifika.ID_Container' cannot be a foreign key and a unique field at the same time.
Why should this not be possible? If ue UNIQUE is set in the target table (ContainerDetails) one could only open a single entry for each of the records in Container in the ContainerDetails .... done .... but this seems to be impossible from the AppGini settings.
Of course, I can adjust this later on in the database (changing table structure of ContainerDetails), but it seems to me that either I am doing some AppGini handling wrong, or it's an unjustified error (in my use case).

Why two tables? I want to have a clean view for one usergroup, but another usergroup should see more details. I could do this with hooks by hiding these unwanted fields, but it seems much straighter to me not display them at all (never trust user input ... and such).

If you suggest some other way to realize my situation with AppGini, great. As I am quite new to AppGini, you might also suggest using the hooks variant with only one Container table which holds some more details than it does at this time.

Olaf

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

Re: Build 1:1 relation with drop down... impossible?

Post by jsetzer » 2019-06-05 10:37

This is one way to solve that requirement:
  1. model a "normal" 1:n relationship in AppGini with lookup field ContainerDetails.Container_ID referencing Containers.ID
  2. remove Insert-permission from your group(s) (except 'Admins'). Then you can only insert records into ContainerDetails by code.
  3. after insert of a new Container, automatically create a new ContainerDetails record by SQL. Don't forget to set the owner after inserting the new record using set_record_owner() function.
Should do the trick. I did something like this for additional user-settings (and for client-settings) where each user needed exactly one record in a specific usersettings-table.

You may consider adding a UNIQUE CONTRAINT in your ContainerDetails table.

Hope this helps!

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

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

Re: Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-05 10:51

Hi Jan,

thank you for your suggestion. I understand, that here is a limit from AppGini. Unfortunately I can not simply hide/completely drop fields from view based on usergroup. Too bad.
I am not sure, if I will go that road or actually use a single table for all specific Container data, but use hooks to hide the fields from certain groups.

Thanks again, really bad, that there seems no easier (i.e. in-AppGini) solution.
Olaf

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

Re: Build 1:1 relation with drop down... impossible?

Post by jsetzer » 2019-06-05 11:07

>> Thanks again, really bad, that there seems no easier (i.e. in-AppGini) solution.
You are welcome!

I know that BigProf has "hooks for fields" on their RoadMap. Having these hooks we could hide fields or making fields readonly and more depending on programmable conditions in PHP. We've been talking quite a long time about that feature on the phone a while ago. Right now there are other priorities for version 5.80. But I really would like to have field-hooks, too.

You can always use JQuery in TABLENAME-dv.js, of course, to hide or lock (readonly) fields. So you can avoid having a second table.

Best,
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

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

Re: Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-05 11:14

Hi Jan,

as probably said, I am quite new to AppGini. I will look into the TABLENAME-dv.js sugesstion.
Olaf

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

Re: Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-24 16:58

Hi Jan,

you suggested
You may consider adding a UNIQUE CONTRAINT in your ContainerDetails table.
above. I think this is a very easy solution - but with the disadvantage, that it will always show up as a mismatch between AG and the actual database in the Rebuild Fields-Admin sections.

I will submit this as an annoyance.
Olaf

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

Re: Build 1:1 relation with drop down... impossible?

Post by jsetzer » 2019-06-24 17:23

Hi Olaf,

hmmm, I never had a situation in which Admin-Area / Rebuild Fields has marked existing UNIQUE CONSTRAINTS as a deviation. Are you sure?

Best,
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

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

Re: Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-25 06:12

Hi Jan,
I believe I am (see image).
The first: AG says field can not be unique, so I needed to follow AG's requirements. In the database I then set the contraint (index to unique)
The second: Is a view. I use that (like you suggested) to have a lookup field in a table that shows all memberID's. In AG I needed this "table" to have a PK (obvious) - but my "view" has - naturally - no PK.
ec34b.png
ec34b.png (91.42 KiB) Viewed 4051 times
[edit]Improved image[/edit]
Olaf
Attachments
ec34.png
ec34.png (196.44 KiB) Viewed 4051 times

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

Re: Build 1:1 relation with drop down... impossible?

Post by jsetzer » 2019-06-25 06:19

Jan:
You may consider adding a UNIQUE CONTRAINT in your ContainerDetails table.
Olaf:
[..] but with the disadvantage, that it will always show up as a mismatch between AG and the actual database in the Rebuild Fields-Admin sections.
Hi Olaf,

there is a deviation because of the primary key flag, this is obvious, but NOT because of a unique constraint as I suggested. So as I said, AppGini does NOT mark an existing unique constraint as deviation, because it does not have information about unique constraints in the project file, so Rebuild Fields cannot mark them.

I think this was a misunderstanding.

Best,
Jan
Last edited by jsetzer on 2019-06-25 06:27, edited 1 time in total.
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
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-25 06:24

Hi Jan,

what about the first case ... ? Or did you mean it the other way around?
Olaf

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

Re: Build 1:1 relation with drop down... impossible?

Post by jsetzer » 2019-06-25 06:36

Hi Olaf,

yes, obviously AppGini will show a deviation. As mentioned before, this is not because of the unique constraint, but because auf the PK-flag.

But what exactly does not work in your implementation?
Does any insert, update or delete crash?

Best,
Jan

PS: I did this couple of times but never had an exceptional crash due to an additional unique constraint.
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

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Build 1:1 relation with drop down... impossible?

Post by pbottcher » 2019-06-25 07:18

Hi,

maybe I did not get the picture correctly, but why do you not use the advance SQL query to reflect the 1:1 relation. Provide through the SQL only the available PK' s of the Container table to the dropdown in the ContainerDetails.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-25 11:33

Hi you both,

@pbötcher, this "problem" is not about the SQL for the containers (container-places) you helped me with.

I guess I am trying to make two (or a different point):

Concerning ID_Usersettings.
I agree pbötcher, that I could to that in SQL but
a) would AG still show all names in the list view of the table, even those that are not available anymore (because they have been used already)? I believe not (though I admit, not trying out again - I think I had a similar problem in another table and that did not work - but correct me, if I am wrong).
b) There is no reason for AG to prevent me setting this type of constraint, at least none that I could think of - as it makes perfectly sense.
c) solving this with a SQL approach is imho working around existing possibilities of table/field definition, inventing the wheel again (and from experience, making things worse in the long run).

Concerning memberID
AG forces a PK when you want that table as source for a lookup, so I set the PK in AG, but in the database a view can not have a PK.... It's just confusing to have that warning with no way to remove it anyways. I admit, that I do not have an idea how to solve that for good.
@Jan: My generated code does not crash (create, update, delete work), but in rebuild fields you can not get rid of the memberID warning that in the database it has no PK.

Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Build 1:1 relation with drop down... impossible?

Post by pbottcher » 2019-06-26 07:17

Hi,

for a) yes if you create the correct SQL :-).

for b) yes, but the devolper decided to start with something. So I would not call this a bug/annoyance, but a feature request. The product was not yet designed to support this, so it should be a new feature.

for c) nope, it just uses the actual possibilities of the product. As said in b, there are always limitiation to a product and if you want to get more, put in a feature request to improve the product. Until that, work with what the product provides you.
Of course there are a lot of limitations, but still also a lot of possibilities to acheive what you want to do.

As far as VIEWS are concered, this is not really supported by AppGini nativ, so it is a workaround to acheive certain goals. Not sure what the issue is if it shown in the rebuild fields, as this is just an information, but no restriction and the app works fine.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

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

Re: Build 1:1 relation with drop down... impossible?

Post by onoehring » 2019-06-26 08:17

Hi pbötcher,

ad a) interesting, I will need to check some SQL at some time :-)

ad b) yes, you are correct, and I agree. Sometimes developers have to make decisions which are strange to newcomers (like me) and also strange when the product is continually developed. At this time the developers might have made a different choice.

ad c) yes, you are so right (speaking as a developer myself :-) ) ... and yes, there are a lot of possibilities with AG. I guess I am scratching the surface at this time.

Olaf

Post Reply