Lookup join.
Posted: 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.2
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
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..
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.2
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
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..