1 2 Previous Next 25 Replies Latest reply on Sep 15, 2016 7:39 AM by amerritt

    xlsx file import succeeding but not displaying results.

    amerritt

      I currently have a script set to import a .xlsx file and it is supposed to populate a table.

      On a layout I have a portal set to view the contents of the table after the import.

       

      My problem is that the import is running and showing "x records created, 0 skipped" etc but nothing is displaying in the portal.

       

      Its showing 1 record where all fields are blank, and then a blank record below that. (There is always 1 blank record at the bottom, even when a new table is created with nothing to populate it).

       

      Whats so frustrating is that I had it "working" earlier and I have no idea why it isn't working now.

      I've tried reverting all changes since I last had it working, but I must have missed something somewhere.

       

      Any troubleshooting advice would be appreciated.

        • 1. Re: xlsx file import succeeding but not displaying results.
          David Moyer

          Hi,

          just real quick - you can't really import into a portal, you import into a table.  So please investigate that path.

          • 2. Re: xlsx file import succeeding but not displaying results.
            David Moyer

            eesh - that sounded assholic.  My bad.

            All of the records imported would need to contain the same parent key to show in the portal.

            • 3. Re: xlsx file import succeeding but not displaying results.
              philmodjunk

              And if there's a portal filter in place, they also have to "pass the filter".

               

              So go to a layout based on the portal table and look to see what the imported records look like and then you may be able to figure out why they didn't appear in the portal.

              • 4. Re: xlsx file import succeeding but not displaying results.
                fmpdude

                I have an ongoing problem where there is an Excel file, just a regular ".xls", that's created from an export from another program that I try to read into FileMaker.

                 

                If I try to import it, nothing happens.

                 

                The only "solution" is to pre-open the file in Excel and save it.

                 

                * Weird.

                 

                * Bug.

                 

                * Been happening for a long time.

                 

                Not sure if that's your issue or not, but from your posting, it sounds like it might be if the xlsx file was possibly being generated a different way. It wasn't clear from your posting if the data was being imported but not displaying or just not getting imported at all.

                 

                HOPE THIS HELPS.

                • 5. Re: xlsx file import succeeding but not displaying results.
                  philmodjunk

                  fmpdude,

                   

                  Can you get it as an .XLSX file instead? Is the file created from either an older version of Excel or some other software? It's not unusual for other software to save data with the right extension for Excel but get some detail wrong that causes a problem for other software such as Filemaker accessing the same file. Opening in Excel is a common "fix" for that issue.

                   

                  I seem to recall that Google Sheets can cause this issue...

                  • 6. Re: xlsx file import succeeding but not displaying results.
                    amerritt

                    moyerdave, I think I said in the OP that the import was supposed to populate a table. So, for instance I have a table "Profile" created with some fields "FirstName, LastName, Age, Zip" things of that nature. I then have a different table "Events" which Profile inherits from. I create a new record in Events, using a Primary key "EventName". In the import script I set the key "EventName" to a new Profile table, which has a field for each record called "EventAttended". This is a rough idea of how I have it set up.

                     

                    philmodjunk, I have no portal filter (anymore). That was the first thing I checked. I guess I could go to a table view layout displaying what the portal is supposed to display, I'll have to try that.

                     

                    fmpdude, I think you may have hit the nail on the head. The file is a .xlsx file, but I have viewed it in OpenOffice for Mac without saving. I wonder if its a similar problem.

                    • 7. Re: xlsx file import succeeding but not displaying results.
                      David Moyer

                      Excel is a cuttlefish.

                      I should back that up:

                      Try typing 9/14 into a cell and see if you can make it remain "9/14".  Maybe Excel has been fixed, but in the past, I've spent way too much time struggling and failing to format data the way I want.

                      • 8. Re: xlsx file import succeeding but not displaying results.
                        fmpdude

                        The output file is an XLS version 5 from Visual FoxPro when exporting a cursor to "TYPE XL5". Since Visual FoxPro is defunct no further versions will be forthcoming.

                         

                        "XL5" (xls) was probably the most commonly used output format back then, though an old format by today's standards.

                         

                        Maybe I will try CSV instead.

                         

                        Thanks Phil,

                        • 9. Re: xlsx file import succeeding but not displaying results.
                          fmpdude

                          If you weren't able to actually import any data, try opening the file in Excel, saving it, then try to import it. Let us know how that goes.

                          • 10. Re: xlsx file import succeeding but not displaying results.
                            amerritt

                            Ok, new update. The information is storing, I just cannot for the life of me get any of it to display in the portal.

                             

                            Here's what I have.

                            Table: Training_upload

                            PrimeKey: Training_ID (Auto enter on creation)

                            Other fields.

                             

                            Table: Training_profile

                            PrimeKey: Training_Profile_ID (Auto enter on creation)

                            Field: First_Name

                            Field: Last_Name

                            Other fields.

                             

                            I have a relationship drawn from Training_upload::Training_ID to both Training_profile::First_Name AND Training_profile::Last_Name.

                            This creates a 1..n relationship from Training_upload to Training_profile.

                             

                            Then, on a layout with Training_upload as the selected table, I have a Portal connected to the "Training_profile" table with fields such as Training_profile::First_Name displaying in the portal. There are no filters on the portal and I have deleted and re-created this portal.

                            This portal remains blank.

                             

                            I made a temporary layout selecting the Training_profile table and the fields populate with the information from the import.

                             

                            What is possibly wrong with this setup?

                            • 11. Re: xlsx file import succeeding but not displaying results.
                              beverly

                              Please refrain from insulting cuttlefish.

                              • 12. Re: xlsx file import succeeding but not displaying results.
                                philmodjunk
                                I have a relationship drawn from Training_upload::Training_ID to both Training_profile::First_Name AND Training_profile::Last_Name.

                                Don't see how that relationship could possibly work. IN order for records to match via this relationship and thus appear in a portal that is based on it, both the text in First_Name and the text in Last_Name would have to be exactly the same and also exactly the same as the text in Training_ID.

                                 

                                In other words if all three fields contained the exact text of "Smith", then your records will match. But if you have a first name of "John" and a last name of "Smith" that relationship will not work because the value in Training_ID has to match the value in First_Name AND the value in Last_Name and that's impossible with first_name = "John" and Last_name = "Smith".

                                • 13. Re: xlsx file import succeeding but not displaying results.
                                  amerritt

                                  I must have an intrinsic error in how I'm viewing the relationship modeling. It was my understanding that one to many implied that One Training_ID belongs to (up to) many names. In this case, relying on both first and last names. Wouldn't all first and last name combinations belonging to the one Training_ID be shown in the portal?

                                   

                                  To your point, how would you set this relation? Should I create a "Training_ID" field within the Training_profile table and set it to a calculation? Namely set it = Training_upload::Training_ID.

                                   

                                  I guess I don't understand how you could ever get this to work with dynamic record creation, unless you have a script set to run every time you click the "create new record" button within the layout.

                                  • 14. Re: xlsx file import succeeding but not displaying results.
                                    amerritt

                                    I just had a mind boggling thought... Why am I messing with portals in my layout when I can do the exact same thing using fields in a List View layout with Training_profile as the selected table. That might be my best bet, but I'm still curious why things aren't working as I expect them to.

                                    1 2 Previous Next