Lookup fields storing data as integer instead of varchar

[benzoD]

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!

[benzoD]

I've gone ahead and created a csv with the integer equivalents and imported it into the database. In table view the integer value isn't converted to it's text equivalent.

As an example, when creating the record the dropdown lookup is shown as LOC (though it's inserted to the database as 1). Combined with the unit number 5012, it's shown as LOC5012 in detail view. When displayed in table view, it becomes 15012.

Does anyone have any comments on this? Has anyone else run into this and worked around it? This is really throwing a major curve into deploying this app.

[a.gneady]

I understand that the main impediment here is that you want to import a CSV file into a table that contains lookup fields ... the file contains the actual display values while the table is expecting integers for those lookup fields. If that's the case, the work-around for streamlining this would be to create a customized import script that would parse the CSV file to translate the actual display values into their equivalent IDs before inserting them into the table.

[KSan]

Excel is your friend... Prep your data before import and you can workaround this challenge.

[benzoD]

Well, my last post was about my attempt to do what you've both suggested. I actually created a CSV with the IDs instead of the display values but the IDs aren't converted back to display values when viewed in table view. So instead of creating a nice display value of LOC(which is actually value 1 that has been imported via CSV) 5012 (the number that specifies which office at LOC we're talking about), it displays 15012 . What should be LOC5012 is showing as 15012. I am just curious as to why the imported integer values aren't converted to the correct display values in table view, which is where most people are going to be working.

[a.gneady]

Hmm ... that's weird .. Could you post one or two lines from the CSV file, please?

[benzoD]

Sure:

id location type spaceID code
1 1 Office 400 AIR
2 1 Office 402 AIR

I included the header row with field names.
Thanks for your help on this, a.gneady!

[a.gneady]

I'm so sorry for the long delay. If you're still experiencing this issue, or in case someone else comes to this page, try to format the CSV file using comma as separator. So, for the above lines, they should be formatted like this:
id,location,type,spaceID,code
1,1,Office,400,AIR
2,1,Office,402,AIR

[benzoD]

Thanks for the reply. Actually I wiped everything out, rebuilt the app in AG, reinstalled and the issue is fixed.