Unanswered: Importing Data From EXCEL to a Smple Drop-Down List Problem
I am having a very basic problem but I have no idea what is wrong!!! I have a form that has a 'Simple Drop-Down List'. This drop-down is for gender and contains two values - 'male' and 'female'. Once I have exited the form editor, clicking on this reveals to two choices and I can select either gender, male or female... this much works fine.
The issue is when I import data from EXCEL. I am importing approximately 8,000 records and one of the fields is gender (on the EXCEL worksheet is is listed as 'male' and 'female'). After downloading, all the information in Brilliant Database is fine EXCEPT for the 'gender' field. The data is either mostly the selection appearing first in the list (see attached screenshot). In this case, 'female' is listed first at about 95% of the entries will appear as 'female' in the gender field. Conversely, if 'male' appears first, about 95% of the entries will appear as male - this does not match what the imported data is as it is approximately 50% male/50% female. I am at a complete loss at to what is going on here and as to why the information is not importing properly. Oddly, if I leave the 'Simple Drop-Down List' values blank, everything imports perfectly. Has anyone experienced any strange behavior with a 'Simple Drop-Down List' when importing data? Am I doing something wrong?
When you create a new empty record, does the gender field auto populate with a value or is it blank?
If it's displaying a value, that **might** be the problem.
Edit your drop down list and put an empty line in over Female. Now exit the form editor and create a new empty record. Set the gender drop down list to the new empty value you entered. Finally, right click on your form and select “Set as Default”.
The other thing to remember, you probably already know, is that there is no connection between the drop down list of items and the actual value stored for that field in the record (other than it being used to initially set the value). So if this continues to be a problem, you can safely clear the list prior to import (which you say resolves the problem) and then add the list items back in - this won't have any effect on the value stored for existing records.
When I have a blank form, the 'gender' field is indeed blank. I tried, as you suggested, just placing an empty line over the first entry (originally I had it as the last entry but removed it when things went awry). It worked properly!!! Many thanks.
Yes, that is a very wise point. I already have a means to validate all incoming data. The downloads will be coming quarterly at the rate of about 8,000 records per time. Some of the fields can have a massive array of acceptable values but no improper values are allowed. I added some scripting, queries, and 'magic' to ensure that all incoming information is of the allowable type... this capability far exceeds what is available through the mask option. Once the information is checked, it is not checked again unless a change is made for quickness of operation. All information that is not proper is flagged and consolidated so that it can be investigated and corrected. No validation that I am aware of has the means to see if the field was supposed to be 'male' when it appeared as 'female' after the download... that was really odd to see that happen. Luckily, the fix was easy.