Lookup fields storing data as integer instead of varchar
Posted: 2013-11-07 13:19
I have two tables in my database, "location" and "space". "location" stores info on locations in our our organization, and "space" stores info on the individual units or spaces in each location (offices and parking spaces). "location" has a field, "code", that stores a code that combines with "space.number" to name each individual part of each location. So Location 1, office 1 is named L1O1, parking space 1 would be L1S1, etc.
Now, the "space" table has 2 lookup fields, one that allows me to choose from the list of locations and the other that automatically assigns the code based on the location chosen. When you enter the space's location and number, it combines the code and number which is referenced in other parts of the app.
Ok! So far so good.
My problem is in trying to import this info from a csv. I have literally thousands of these spaces to assign, and importing via csv is the only feasible way to do this. The issue is that the "code" and "location" lookup field is automatically assigned an Integer data type by AppGini because the PK of the parent table is Integer, which means that even though each piece of info is originally varchar/text, the data is converted to a number. So Location 1 becomes 1, Location 2 is 2. Location 1's code is also stored as 1 instead of L1.
I created a csv with the location names and codes and imported it. The preview showed exactly what I expect it to do but when looking at the "spaces" table view the location and code are blank. When I take a look in the database, I see the location and codes entries are all converted to 0s. I've discovered that ALL lookup fields with a parent table with a Integer PK (which is assigned automatically by AG) are being stored as integers. I tried to change the PK's data type to varchar (because I really want to refer to the locations and codes by their easily remembered names instead of having to convert them all back and forth between their names and their integer equivalents) but any auto-incrementing field must be Integer so that doesn't work.
How can I make the data type for each of these fields stay varchar? Is this a limitation of AG or MySQL in general? Thanks for any help!
Now, the "space" table has 2 lookup fields, one that allows me to choose from the list of locations and the other that automatically assigns the code based on the location chosen. When you enter the space's location and number, it combines the code and number which is referenced in other parts of the app.
Ok! So far so good.
My problem is in trying to import this info from a csv. I have literally thousands of these spaces to assign, and importing via csv is the only feasible way to do this. The issue is that the "code" and "location" lookup field is automatically assigned an Integer data type by AppGini because the PK of the parent table is Integer, which means that even though each piece of info is originally varchar/text, the data is converted to a number. So Location 1 becomes 1, Location 2 is 2. Location 1's code is also stored as 1 instead of L1.
I created a csv with the location names and codes and imported it. The preview showed exactly what I expect it to do but when looking at the "spaces" table view the location and code are blank. When I take a look in the database, I see the location and codes entries are all converted to 0s. I've discovered that ALL lookup fields with a parent table with a Integer PK (which is assigned automatically by AG) are being stored as integers. I tried to change the PK's data type to varchar (because I really want to refer to the locations and codes by their easily remembered names instead of having to convert them all back and forth between their names and their integer equivalents) but any auto-incrementing field must be Integer so that doesn't work.
How can I make the data type for each of these fields stay varchar? Is this a limitation of AG or MySQL in general? Thanks for any help!