Lookup join.

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
Jay Webb
Veteran Member
Posts: 80
Joined: 2017-08-26 15:27
Contact:

Lookup join.

Post by Jay Webb » 2019-07-23 18:11

I'm using AppGini 5.76

Here is my current tree (image.1) table "Marriages" field "FFN" gets its lookup from table "File_Surname_Number" and is to be compiled from table "File_Surname_Number" fields "FF_SURNAMES" and field "FF_NUMBERS". (image2)
Image.1
Image

Image.2
Image

Field "FF_SURNAMES" looks to table "Add_a_Surname" field "SURNAME_lIST" this field is unique.
Field "FF_NUMBERS" looks to table "Add_File_Numbers" field "NUMBERS" this field is unique. (image3 & image4)

Image.3 & Image.4
Image Image

Question: how to bind surnames groups to number groups,
example; surname 1, is grouped to numbers A.001 - A.2000, surname 2, is grouped to numbers B.001 - B.2000, surname 3, is grouped to numbers C.001 - C.2000 and so on, there really codes and not just numbers, A.001, B.001, C.001 and so on and they need to be unique.

What's needed is, when adding a new number, I would like to select a surname and then display the next avialble number/code for that surname, so if I'm adding a new number/code to one of the unique surname I can only add the next avialble number/code for that surname. At present there are 22 unique surnames and a few more could be added over time and they would need to have number/code assigned.

Question: Would I need to add a field to table "Add_File_Numbers" like "SELECT_SURNAME" and make it look to "SURNAME_lIST"? then use a hook? or use an sql trigger? or a script? not sure how to do this or even if it can be done.

I hope I've explained my issue clear enough for someone to help, I'm stumped.

Thanks..
What we envision, we make happen.

User avatar
Jay Webb
Veteran Member
Posts: 80
Joined: 2017-08-26 15:27
Contact:

Re: Lookup join.

Post by Jay Webb » 2019-07-23 22:36

Well, I figured how to make the join, made another field "SURNAME" in Add_File_Number, that is a lookup to "SURNAME_LIST" to populate as selections.

But haven't figure how to limit surnames to specific code groups for "NUMBERS' in Add_File_Number.

I'm thinking JS in magic file is the best route but I know even less about JS then appgini.

Any suggestions?
What we envision, we make happen.

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

Re: Lookup join.

Post by onoehring » 2019-07-24 09:14

Hi Jay,

did you try to use "advanced" SQL and use the WHERE ... in your SQL condition?
Or did you mean something like https://bigprof.com/appgini/screencasts ... th-appgini ?

Olaf

User avatar
Jay Webb
Veteran Member
Posts: 80
Joined: 2017-08-26 15:27
Contact:

Re: Lookup join.

Post by Jay Webb » 2019-07-24 16:08

Hello Olaf

The video was perfect, that's just what I'm trying to do and I have success, knowing what to call it was half the trouble, took me 3 tries
to get it to work but it works.
Now just need to figure how to make all CSV files to populate with all the data I've already collected.

Thank you so much for pointing me in the right direction!
What we envision, we make happen.

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

Re: Lookup join.

Post by onoehring » 2019-07-24 18:07

Hi Jay,

the CSV should contain the things you see in your table view.
You can also take a look here to output your totally custom SQL: viewtopic.php?f=2&t=3116#p10491

Olaf

User avatar
Jay Webb
Veteran Member
Posts: 80
Joined: 2017-08-26 15:27
Contact:

Re: Lookup join.

Post by Jay Webb » 2019-07-25 00:48

Hi Olaf

Yep, most of it is ID numbers except for the surnames and codes.
Would you now of a way to limit the code/number being used once per table, (ie) Marriages, Baptism_Birth, Deaths.
I made the table FF_Codes, field Codes unique but that only prevents it from being added again in the FF_Codes table.
This FF Code is a linker from the Marriage record, to the Birth record of the children, to the Death record.
The idea is,
Doing a search with the FF Code will bring up the whole family, father, mother, sons, daughters, grandchildren, great grandchildren and so on.
So really it's only in the Marriage table and Death table that needs to be limited to one code per marriage sense this is where the family starts, and death is where it ends the in the Births it could be used a 100 times.

I have another appgini/mysql genealogy database of members submissions genealogies number over 10,000 entry's.
This new system is document lookup system each entry is supported by documentation with comments and upload.

Any ideas how to limit FF code on just 2 tables?
What we envision, we make happen.

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

Re: Lookup join.

Post by onoehring » 2019-07-25 06:01

Hi Jay,
I am not sure if I understand you correct.
Maybe there is an error in your database design?
Each person can have exactly 1 birthday, but multiple marriages.
So your tables should have something like a person table which holds specific data for each person and a person can have this data (like birthday) only once. Other tables (marriages) should have a foreign key (the primary key, probably FF_Codes from another, e.g. the person table).
In case that you want to have a person (who is always child of two persons) being linked to those by adding their PK as FK to the mother and father.
This means for your case (if I get it correct): marriage is actually not unique.
Your marriage table should be something like

Code: Select all

ID_marriage (PK)
ID_person1_who_is_marrying (FK - this is FF_Code of some other person)
ID_person2_who_is_marrying (FK - this is FF_Code of some other person)
.
.
Now to your wish to search for everyone.
Maybe there is an easier way to search "all" tables (but this is something you probably do not want). Imho you will need several SQL strings to search a table after another for the FF_Codes you are looking for.
Write your results to some array (of a temporary table) and output them afterwards - or (but you will need the same fields) use a SQL UNION to join your results into a single query. This UNION could be your CSV source.

If this is not what you want, I read you post again: Death details should be in the person table ...or in an extra table like

Code: Select all

ID_person_death(PK)
ID_person (FK - this is FF_Code of the person whos death is described here)
death_date 
way_of_death (or whatever fields you have)
.
.
This way, you can put a CONTRAINT on ID_Person to be only once in this table. In AppGini this will not work at this time as you would be creating a 1:1 relation between person and person_death tables. You will need to create this contraint directly in the database and ignore the Rebuild Fields warning in the generated files for this fields inconsistency. As a result you will be able to have exactly ONE entry per person in the death's table.

Olaf

Post Reply