1 2 Previous Next 17 Replies Latest reply on Feb 6, 2014 9:52 AM by philmodjunk

    Question on importing from EXCEL

    Sunny1

      Title

      Question on importing from EXCEL

      Post


           I have a previously designed database that has some fields as check boxes.  I have to have outside sources submit information on spreadsheets that I import.  How do I get fields from the spreadsheet that say Yes or No or X (Indicating yes) to come over as check boxes in my database? 

            

           Thank you for your help in advance.

        • 1. Re: Question on importing from EXCEL
          davidanders

               The CheckBox is a format (like Bold or Underline) the field actually contains Yes.

               You can see this by creating another instance of the field next to an instance that is formatted for CheckBox.

               If the field can have several checked Checkboxes the content of the field will be "Yes, No, Black, White"

          • 2. Re: Question on importing from EXCEL
            Sunny1

                 I tried importing a sample spreadsheet and this field does not come over.  What am I doing wrong?

            • 3. Re: Question on importing from EXCEL
              philmodjunk

                   I suggest importing the data into a field of type text and inspecting the imported data before trying to give it a checkbox format. 

              • 4. Re: Question on importing from EXCEL
                Sunny1

                     Thanks I will try that.

                     One other thing.  When I do a test import, and let's say I have created two records on the spreadsheet.  I match the fields I have on the spreadsheet to the fields on the database.  I have more fields in the database than what I am asking the spreadsheet to contain because some info has to be entered after to the import.  So, when I import the two records with about 10 completed fields, 23 blank records come over into the test database and I can't figure out why these blank records are coming over. 

                     Any ideas where to start looking?

                • 5. Re: Question on importing from EXCEL
                  philmodjunk

                       Do you have blank rows of data above your "10 completed fields "?

                  • 6. Re: Question on importing from EXCEL
                    Sunny1

                         no, they come out underneath the 2 full records that I produce in the test.  So, if I make up two records, fill in all the fields I have info for and import it into FileMaker, the records show 26 records but only two have info.  I can even see this when I am on the Import File Mapping screen, two records have info, and 24 are blank.  I don't know where they are coming from.

                    • 7. Re: Question on importing from EXCEL
                      philmodjunk

                           Neither do I. FileMaker will import data from excel but stops with the last row in the spreadsheet that contains data. Are you sure that ever cell in those rows are empty? They don't even contain data that is not visible such as a space character?

                      • 8. Re: Question on importing from EXCEL
                        Sunny1

                             Well, I tried something different and I think I solved it.  Rather than using the List View to generate an Excel spreadsheet to use as my test, I recreated a blank Excel spreadsheet and now it only brings over the records I add to the spreadsheet.  There must be a field in the database that created blank records and when I use the List View to generate a spreadsheet, it brought over the field type or something.

                             Anyway, In this database, if I was to create a record through New Record, two fields auto enter the school year and a field that will generate a total of records (Students)  When I import these students into my database from the Test Spreadsheet, these field do not auto enter.  Is there a way to do this or do I have to do it manually once they are imported?

                              

                        • 9. Re: Question on importing from EXCEL
                          Sunny1

                               Another question:  Need to write a function to look at the month and date of a birthday field and compare it to a beginning and ending date of a current week and if it falls within that week, have it return an X or Yes in a field.

                                

                               Reason:  When a student's birthday month and date will occur when they are at a day camp, they want to indicate that a birthday celebration should be done that week and this prints on a report.

                                

                               How do I write that?

                          • 10. Re: Question on importing from EXCEL
                            philmodjunk

                                 I will assume that you have an unstored calculation field, Birthday that uses the camper's birth date to compute their birth day...

                                 Let ( [ T = Get ( CurrentDate ) ;
                                            Sunday = T - DayOfWeek ( T ) + 1 ;
                                            Saturday = Sunday + 6
                                          ] ;
                                            If ( BirthDay > Sunday and BirthDay < Saturday ; "X" )
                                        )

                            • 11. Re: Question on importing from EXCEL
                              Sunny1

                                   Not exactly.  There is two fields that represent the beginning date and ending date of the week that they are at camp.  (02/01/14 - 02/06/14I can record their birth date in an unstored calculation field that would take their birthdate and see if it falls within the week they are at camp, if yes, it would place an X in the Birthday field. (if their birthdate is 02/05 then it would place an X in the Birthday field. 

                                    

                              • 12. Re: Question on importing from EXCEL
                                philmodjunk

                                     BirthDate is the day they were born. I don't think any of your campers are being born at camp wink

                                     But do you have a calculation that takes the date a camper was born that then computes the date of their next birthday?

                                • 13. Re: Question on importing from EXCEL
                                  Sunny1

                                       no, do I need that?

                                        

                                  • 14. Re: Question on importing from EXCEL
                                    Sunny1

                                         If you could help me write that, that would be fantastic.  So I would need a field to take the student original birthdate, convert it to when next birthday is and then determine if that date falls within the week they would be at camp.

                                         Thank you so much for your help so far.

                                    1 2 Previous Next