LOOK-OUT FIELD HELP!

[sblasic]

Hi Girls and Guys,

I'm trying to make an AppGini application for contacts of medical workers.

Have total of two (2) tables - both tables are initially imported/populated from XLS file (IMPORTANT!)

1st table -> is a simple contact data table (fields: Name, Medical specialities, working hours, telephone...)

2nd table ->containing only one field: medical specialities (surgeon, dermatologist, ophthalmologist etc.)

***
I want that the FIELD "Medical Specialities" of the FIRST table (main table) to be populated with the data from the second table (Medical specialities).

When I set-up that field: medical specialities from the FIRST table, to be the LOOK-UP field - and the PARENT TABLE to be that 2nd table (Medical specialities).- all the data from the Medical specialities from the main table (contact table) disappears (empty Medical specialities field)!
(Pls. see the attached screen-shots)

So, I guess my question is:

How to preserve that existing (CSV imported) Medical specialities data in the Medical specialities field of the MAIN TABLE and - at the same time - facilitate entering a NEW CONTACT - so that an user can easily choose from the drop-down list a specific Medical speciality (linked from the 2nd table)?

I really do hope I was clear enough - pls. contact me if you did not understand what I'm trying to say - thank you!

[onoehring]

Hii,

probably your database looks like this:
Your first table holds "medical specialities" as a text field.
Your second table (as well as the first) might have received a new field - which could probably be an integer (autoincrement, primary key).

Now, when you set in table 1 the field to be a lookup from table 2 AppGini takes the primary key field of that table - which could be an integer now. As the data in table 1 does not relate to the data in table 2, field PK, the field is displayed empty.

You could try to:
- delete the integer primary key field from table 2
- set the field in table 1 as a lookup again (from table 2) (so AG knows it has to use a different field (type)
Then recreate your application. Make also sure to clear your browser cache! This could work, as AG should then draw the actual text from the table 2 for the lookup in table 1.

well, or ... Excel is no database! You should think of creating a relational database schema for your database an actually use autoincrement primary keys

Olaf

[pbottcher]

Hi,

just a thought. Did you import first your data and change the field definition afterwards to be a lookup? If so, you had a text in your Medical Specialities and you tell the app now to look for a reference which cannot be found any more. So the data will not be displayed.

If this is the case, clear your database, reload the 2'nd table first and afterward load the first table.
(BTW: I assume you are using the Appgini build in load function for the data, not a direct db load).

If you cannot do that to whatever reason, you need to rebuild the fieldvalues for the Medical Specialities field.

Assuming that you have for TABLE2:

ID as the primary key
FILENAME as the field that holds (surgeon, dermatologist, ophthalmologist etc.)

You could try to run

Code: Select all

UPDATE TABLE1 set FIELD_Medical_Specialities = (select  PK from TABLE2 where FIELDNAME=TABLE1.FIELD_Medical_Specialities)

via phpmyadmin or MySQL Workbench, or any other mysql admin tool.

Replace TABLE1, TABLE2, FIELD_Medical_Specialities , PK and FIELDNAME with our correct definitions

Code is not tested and usage of the code shall be done on your own responsibility. Always make a backup of your data first.

[sblasic]

@ Olaf and @ Pböttcher

Thank you very much - and sorry for the late reply.

I'll work according to your suggestions and report my findings.