One to One Relationship

[yonder]

Hi there,

I have 2 tables;
Table A : id, firstname, lastname
Table B : table_A_id (it will be lookup field), company

I insert 2 records to table A as;
id=1, firstname=onder, lastname=yilmaz
id=2, firstname=under, lastname=speed

Then i want to insert a record to table B and the record must be relation with Table A as one to one. I mean if i select the record 1 from the table A and insert the record, then i want i cannot add another record to table B as lookup with table A record 1.

Is there any way for this?

[pbottcher]

Hi,

cant you just set the table_A_id in table b as unique?

[sclifton]

Off the top of my head: You could either:

1. Create a separate unique index on attribute table_A_id in Table B, so any Insert attempt for the same value fails (although I believe existing error handling in this situation is less than totally user friendly).

2. You could add code to the hook "Table_B_beforeinsert for Table B which does a SELECT table_A_id FROM Table_B WHERE table_A_id = 1 (as an example). If result returns null, just return TRUE for the hook call which then permits the Insert to proceed; If SELECT returns a non-null numeric value, then you know a record already exists in Table B for Table A record 1, so you return FALSE for the hook call, so the new INSERT never happens.

https://bigprof.com/appgini/help/advanc ... ore_insert

Hope that helps.

[yonder]

Hi there,

I tried this

Create a separate unique index on attribute table_A_id in Table B, so any Insert attempt for the same value fails (although I believe existing error handling in this situation is less than totally user friendly).

Then i got this error : https://pasteboard.co/JyE4cct.png

So what is problem?

[onoehring]

Hi,

I suggest not setting this in AppGini, but let AG alone as is - and to the change in the database itself. AG will simply ignore it, but the database will make sure there is only once record max per record in the other table.

Olaf

[yonder]

Hi,

I suggest not setting this in AppGini, but let AG alone as is - and to the change in the database itself. AG will simply ignore it, but the database will make sure there is only once record max per record in the other table.

Olaf

Hi onoehring,

Maybe you right but it's not a solution. I hope somebody can help me about it?

[sclifton]

Hi,

I suggest not setting this in AppGini, but let AG alone as is - and to the change in the database itself. AG will simply ignore it, but the database will make sure there is only once record max per record in the other table.

Olaf

Hi onoehring,

Maybe you right but it's not a solution. I hope somebody can help me about it?

I concur that you need to create a unique index on attribute table_A_id using PHPMyAdmin or a similar MySQL management tool (and not AppGini); Any attempt to then create a new Table_B record with the same value for table_A_id will fail but the error message shown by AppGini is not exactly "user friendly" - at least it wasn't the last time that I did a test of that behaviour - hence my original comment.

The alternative approach remains to add code to the table_b_beforeinsert hook so you check before every record insert on Table B to see if a record already exists with the value of table_A_id about to be used to populate a new Table B record on that Insert.

[yonder]

I concur that you need to create a unique index on attribute table_A_id using PHPMyAdmin or a similar MySQL management tool (and not AppGini); Any attempt to then create a new Table_B record with the same value for table_A_id will fail but the error message shown by AppGini is not exactly "user friendly" - at least it wasn't the last time that I did a test of that behaviour - hence my original comment.

How can i do that? Could you show me on PHPMyAdmin?

[onoehring]

Hi,

Warning: If you do not know what to do in phpmyadmin be careful, as you can delete every data you have. You are doing some serious open heart surgery here (if you are a beginner). Your question suggests, that you do not know ... a lot ... about databases and MySQL. I strongly suggest making a backup of your data before starting. I also suggest getting some knowledge about phpmyadmin (alternative: https://www.adminer.org ) if you are considering working with AppGini as there might be other "things" that arise and you can not do in AG directly.

Well, see my image ... if this does not help, please look at the mysql documentation.
Oh, just noticing: Step 0 should be clicking on the table (left side of image) where you want to set the restriction.

phpmyadmin.png (72.34 KiB) Viewed 6044 times

Olaf

[yonder]

Hi,

Warning: If you do not know what to do in phpmyadmin be careful, as you can delete every data you have. You are doing some serious open heart surgery here (if you are a beginner). Your question suggests, that you do not know ... a lot ... about databases and MySQL. I strongly suggest making a backup of your data before starting. I also suggest getting some knowledge about phpmyadmin (alternative: https://www.adminer.org ) if you are considering working with AppGini as there might be other "things" that arise and you can not do in AG directly.

Well, see my image ... if this does not help, please look at the mysql documentation.
Oh, just noticing: Step 0 should be clicking on the table (left side of image) where you want to set the restriction.

phpmyadmin.png

Olaf

Now i do that but i don't understand what next. I publish my appgini file. Could you show me what i will do exactly?

https://drive.google.com/file/d/11muz2v ... sp=sharing

Thanks a lot.

[onoehring]

Hi,

just publish your AG application. If one tries to add more than one entry to target table A (whete you have your lookupfield) for another entry in the other table B, the insert into table A will fail.

Olaf

[yonder]

Hi,

just publish your AG application. If one tries to add more than one entry to target table A (whete you have your lookupfield) for another entry in the other table B, the insert into table A will fail.

Olaf

I tried it but it's not working. You can see my steps;

Step 1 - https://imagesharing.com//photo.php?s=lxq7c6e5
Step 2 - https://imagesharing.com//photo.php?s=qixzfpk1
Step 3 - https://imagesharing.com//photo.php?s=2aiwxm4f
Step 4 - https://imagesharing.com//photo.php?s=gkv87cls
Step 5 - https://imagesharing.com//photo.php?s=6hpcv9zu
Step 6 - https://imagesharing.com//photo.php?s=952ufomb
Step 7 - https://imagesharing.com//photo.php?s=04baqr2j

[onoehring]

Hi yonder,

thanks for the screenshots. I did take look at your axp file (which was not English unfortunately, probably would have helped).
We should get back to basics: What are you trying to do? What is your goal? By this I mean not in AppGini, but what is the connection between your tables. It looks like you are trying to assign a person to a company.
If so, in a company could be more than one person (a person might be in more than one company).

At this time you have a company (table company) to which you can assign exactly ONE person.
But:
At this time you can have company A with person Olaf and company B also with person Olaf.
If you want to prevent this you need to set an unique index in table company on the field where you save the ID for the person.
Please see your own images:

z1.png (243.94 KiB) Viewed 5944 times

z2.png (154.28 KiB) Viewed 5944 times

Olaf

[yonder]

Hi yonder,

thanks for the screenshots. I did take look at your axp file (which was not English unfortunately, probably would have helped).
We should get back to basics: What are you trying to do? What is your goal? By this I mean not in AppGini, but what is the connection between your tables. It looks like you are trying to assign a person to a company.
If so, in a company could be more than one person (a person might be in more than one company).

At this time you have a company (table company) to which you can assign exactly ONE person.
But:
At this time you can have company A with person Olaf and company B also with person Olaf.
If you want to prevent this you need to set an unique index in table company on the field where you save the ID for the person.
Please see your own images:

z1.png

z2.png

Olaf

Dear onoehring,

Thank you for your patience.

My purpose is; one person can be registered to one company. For example;
I have 2 person : Onder Yilmaz, Under Speed
I have 2 companies : Company A, Company B

First i added 2 persons to the person table :
https://imagesharing.com//photo.php?s=d9tflqm7
https://imagesharing.com//photo.php?s=2ujq4vlo

Then i add a company :
https://imagesharing.com//photo.php?s=c513t9lp
https://imagesharing.com//photo.php?s=rzu175he
https://imagesharing.com//photo.php?s=e7uih80m
So i added company as Karadag Elektronik and selected person as Onder Yilmaz.

After that, if i add new record to company table and if i select Onder Yilmaz again, it must be forbidden because i already added a company with person that name is Onder Yilmaz .

I tried your picture :
https://imagesharing.com//photo.php?s=vuhai9ow

But still i can add that records :
https://imagesharing.com//photo.php?s=w5uhi9jr

What's wrong?

[jsetzer]

Hi,

this mySQL tutorial on unique constraints could help:

https://www.mysqltutorial.org/mysql-unique/

Please note: as far as I know from previous versions, when you try to insert a new record using the save button in AppGini detail view and the database server denies this due to a unique constraint, there will be an alert in user interface (UI) but data will be lost. This is not so critical if it is just those two foreign key fields (company, person), that combination already exists anyway, but may be more critical if there are more than those two fields.

[onoehring]

Hi yonder,

you should keep Jan's comment in mind. Doing it the way I suggest is quick and dirty. Maybe check in hooks/tablename/ before_insert function IF the record has an entry in the other table already and then notify/cancel.

Your picture https://imagesharing.com//photo.php?s=vuhai9ow is still showing wrong settings.
The indexNAME is fullname - but the column you have put into this index (the colum that you tell the database you want to be unique is actually ID and not FULLNAME.

Olaf

[pbottcher]

Hi,

reading what you try to achieve, I think the easiest way would be to amend to lookup query (standard AppGini feature) to the employees.

If you adjust the query to just show all employees that are not yet part of a company, you cannot assign an employee twice.

You can use something like

Code: Select all

SELECT `employee`.`id`, `employee`.`name` FROM `employee` where `employee`.`id` not in (select `employee` from `company`) ORDER BY 2

where
company Table has a field employee that is a lookup to the employee table

[onoehring]

Hi,

genius! Bow in awe to the master who's name is passed from generation to generation of AG users and which is to be said pbötcher.
Thanks
Olaf

[jsetzer]

@pböttcher: Good idea and +1 for a simple and built-in solution!

There is only a problem if several users use that form in the same period of time. In this case all users will be offered all open options for selection. All users can make the same selection. All users will be able to save their records, and every save after the 1st one will create another duplicate unless there is a Unique Constraint on the table.

This is probably not a problem for applications that only one person uses. In my experience this will sooner or later lead to duplicates for larger applications in multi-user environments.

Anyway:
This is a good first step and can be combined with a unique constratint for data-integrity reasons.

[yonder]

Hi yonder,

you should keep Jan's comment in mind. Doing it the way I suggest is quick and dirty. Maybe check in hooks/tablename/ before_insert function IF the record has an entry in the other table already and then notify/cancel.

Your picture https://imagesharing.com//photo.php?s=vuhai9ow is still showing wrong settings.
The indexNAME is fullname - but the column you have put into this index (the colum that you tell the database you want to be unique is actually ID and not FULLNAME.

Olaf

Is it wrong? I already selected the ID.
https://imagesharing.com//photo.php?s=dojkyf16

[yonder]

Hi,

reading what you try to achieve, I think the easiest way would be to amend to lookup query (standard AppGini feature) to the employees.

If you adjust the query to just show all employees that are not yet part of a company, you cannot assign an employee twice.

You can use something like

Code: Select all

SELECT `employee`.`id`, `employee`.`name` FROM `employee` where `employee`.`id` not in (select `employee` from `company`) ORDER BY 2

where
company Table has a field employee that is a lookup to the employee table

Hi pböttcher,

In my case, what is the correct SQL query? I tried it but i fail.

[pbottcher]

Hi,

Just open the AppGini app and go to the fullname field in the company table.
Click on Advanced... and insert the code

Code: Select all

 where `personal`.`id` not in (select `fullname` from `company`) 

just before the Order by 2 (with a blank)

[yonder]

Hi,

Just open the AppGini app and go to the fullname field in the company table.
Click on Advanced... and insert the code

Code: Select all

 where `personal`.`id` not in (select `fullname` from `company`) 

just before the Order by 2 (with a blank)

Ah yes, it's working. Thank you so much.

Another problem;

This is probably not a problem for applications that only one person uses. In my experience this will sooner or later lead to duplicates for larger applications in multi-user environments.

What can i do for this problem?

[jsetzer]

Create a unique constraint on your db table using any database tool (like phpMyAdmin or Adminer), as Olaf has suggested, or by using SQL like this:

Code: Select all

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE (column1, column2)

https://www.mysqltutorial.org/mysql-unique-constraint/

[sclifton]

I suspect this will be obvious to most, but given the mixture of ID searches against text values in earlier posts, I thought I'd emphasise the importance of checking a specific person ONLY by using their record's primary key ID against the lookup-field ID value in the company table (in this example), and NOT to try to use a person's "Full Name" text since there may be two or more persons with the same name.

In the UK, this is the "John Smith" effect - hence I advocate choosing a person based on a drop-down display list with their full name and some other characteristic (eg date-of-birth or age) which maps back to their unique ID, so it becomes totally unambiguous to the user which "John Smith" is being chosen to be associated with that company. This also means that a 2nd or 3rd "John Smith" can still be chosen for different company records without problem.

I hope that helps someone.