1 2 Previous Next 16 Replies Latest reply on Aug 11, 2016 12:59 PM by BillisSaved

    Adding additional records from Excel and Guest viewing

    frimages

      This is my first post and I hope I'm doing it correctly.

       

      I'm new to FMP and having downloaded the trial I have only 19 days remaining.

      Whilst I've learned a lot in the past 10 days I still have much to learn and I hope to find some answers through the community.

       

      I am hoping to use FMP to hold a searchable archive for my local history society. We have been scanning documents, photographs, maps etc and have now some 3,500 accessions (lots more to go) held in an Excel workbook. I've imported the Excel file in to FMP and all is well but as the Excel file is constantly being updated as new accessions are added I need to update the FMP database. I've found some help through the community but when I go to 'Import field mapping' and click on 'import action - Add new records', I get the whole excel file added including the original data giving me lots of duplicate records. I only want the new data from Excel.

       

      I intend to use the 'Guest' login for visitors to our history room and I've made a start page outlining a few rules and regulations that visitors must agree to when accessing the archive. A button on the start page takes viewers to the guest viewing page (with find and view only access) but I can't find a way of ensuring that FMP starts up on the 'Start Page'.

       

      A third problem I have is with word files. Inserting as reference only is OK but double clicking on the file name in the container does not open the word document. I have no problem with PDF documents and jpeg files etc.

       

      Many thanks in advance.

       

      Frank

       

      Win10 pro 64, MS Word 2010 FMP 15 Trial

        • 1. Re: Adding additional records from Excel and Guest viewing
          BillisSaved

          Good afternoon frimages,

           

          I hope you had a wonderful weekend. Here a few suggestions:

           

          • Based on the import scenario you described in your first question, it sounds like you may want use the "Update matching records in found set" option. You'll have the option to choose a match field and to "Add remaining data as new records." Visit the following link for more detail: http://www.filemaker.com/help/12/fmp/html/import_export.17.11.html
          • One of the easiest ways to direct a user to a specific layout when the file opens is to write a script with the necessary steps and link it to the "OnFirstWindowOpen" event in the for the file. This setting can be found in File > File Options > Script Triggers.
          • The Word document inserted into the container field with the "Store only a reference to the file" option selected on the "Insert file" dialog should open Word and then the file when you double click on the container or tap the spacebar when the container field is active. You may want to add a button with a script to ensure the file is inserted into the container correctly, rather than using the "Insert file" option from the container contextual menu.

           

          I hope this information proves useful to you. Have a great evening!

           

          God bless,

           

           

          Bill

          1 of 1 people found this helpful
          • 2. Re: Adding additional records from Excel and Guest viewing
            frimages

            Thanks for the quick response Bill.

            It's way past my bedtime here in the UK so I'll check out your suggestions in the morning.

            Thanks again.

             

            Best wishes

            Frank

             

            Sent from my iPhone

            • 3. Re: Adding additional records from Excel and Guest viewing
              frimages

              Hello Bill,

               

              I followed your suggestions and with your help I've got my head around the "Update matching records in found set" option, the "Match field" and the "Add remaining data as new records" and all is well so far.

               

              I can also go to my start layout when opening the file however, an error message "Unknown account name"  appears but all is well If I either 'cancel or OK' the message.

               

              I still can't get inserted word files to open with double clicks or the space bar I'm not too hot on scripts yet so would it be possible for you to explain your suggestion: "You may want to add a button with a script to ensure the file is inserted into the container correctly, rather than using the "Insert file" option from the container contextual menu".

               

              Thanks again - FMP really seems to be the way ahead for my project.

               

              Best wishes

              Frank

              • 4. Re: Adding additional records from Excel and Guest viewing
                philmodjunk

                I still can't get inserted word files to open with double clicks or the space bar I'm not too hot on scripts yet so would it be possible for you to explain your suggestion: "You may want to add a button with a script to ensure the file is inserted into the container correctly, rather than using the "Insert file" option from the container contextual menu".

                There is more than one storage option for container fields and more than one option for how they display. If you want to open the file with a double click and no scripting, Only one combination of those options can make that happen and those options might not be ideal for your situation.

                 

                If you specified "store by reference" when inserting the file and did NOT specify external storage and if you did NOT select "optimized for interactive content" for the container field object on your layout, then double clicking an inserted file should open the file with your computer's specified default application for that file's type.

                 

                But this requires making all of these files at least "read" accessible directly from your computer desktop, so that might not be a good way to set this up.

                 

                On the other hand, a very simple script attached to a button can use export field contents to export and open a copy of the file to the computer's temporary folder. (by exporting to the temp folder, your computer's drive doesn't get cluttered with exported files as the contents of the system's temp folder are not retained.)

                • 5. Re: Adding additional records from Excel and Guest viewing
                  BillisSaved

                  Good afternoon frimages,

                   

                  I hope your day is going well. You will discover as you spend more time working with FileMaker, there are myriad ways to accomplish most objectives. Case in point, philmodjunk's alternate method of achieving your goal, which, by the way, is a nice solution.

                   

                  If you'd like to pursue my suggestion, I think we should verify something first. When you access the contextual menu for the container field in question by right-clicking on it and then select "Insert File...", ensuring that "Store only a reference to the file" is enabled (checked), what happens when you double click on the container after the Word file has been inserted?

                   

                  God bless,

                   

                   

                  Bill

                  • 6. Re: Adding additional records from Excel and Guest viewing
                    frimages

                    Good afternoon Bill,

                     

                    I can't agree more, there seem to be many ways to do many operations in FMP. It certainly is an interesting and absorbing piece of software and the Community is a fantastic resource.

                     

                    Just to verify, when I 'insert' the file in the manner you describe above, double clicking does absolutely nothing! In desperation I even tried dragging the file and also leaving the "Store only a reference to the file!" unchecked, but again double clicking does nothing at all. PDF's and jpegs work just fine in the container.

                     

                    Many thanks for your time and Best Wishes

                     

                    Frank

                    • 7. Re: Adding additional records from Excel and Guest viewing
                      philmodjunk

                      I assume that you are referring to me though I'm not named Bill.

                       

                      I haven't tried this before, but I've attempted to attach a demo file to this post. The container on the left is set up as I described. If you click the button provided, which then insures that you insert a file as I specified, you'll find that you can double click the container field and it will indeed open--provided that the file inserted is one that you could open by double clicking directly on your desktop without using FileMaker.

                       

                      In the second container field, you can right click the field and use any insertion options that you want and you can go into layout mode and change the data display options for the container field however you need. And you can still open and view the file by clicking the button provided for that purpose below it where it exports a copy of the file to your temporary folder and then opens it.

                       

                      You can open the scripts workspace to see how I set up both buttons.

                       

                      Edit note, don't seem to know right from left this morning...

                      1 of 1 people found this helpful
                      • 8. Re: Adding additional records from Excel and Guest viewing
                        frimages

                        Sorry, I thought I'd reply to Bill first and then look at your suggestion.

                         

                        Just after posting I looked at my container via the inspector and changed 'Optimize' from Interactive content to 'Optimize' for Images. Double clicking on the word file now opens the document but insert PDF is no longer available. I really want one large container that will show images and PDF documents along with word files.

                        Unfortunately I cannot see your demo file attached - can you please email to me?

                         

                        Many thanks

                        • 9. Re: Adding additional records from Excel and Guest viewing
                          philmodjunk

                          The file should be there. I can see it here on the web page. Perhaps you are using email to reply? If so, use the link to open the discussion in your web browser and you should see the attached file.

                          • 10. Re: Adding additional records from Excel and Guest viewing
                            BillisSaved

                            Good morning frimages,

                             

                            I hope your day is going well. It looks like you've already checked and changed the optimization settings for your container field, which was going to be my next suggestion. Well done! I can think of a couple ways for you to get the results you mentioned, but it will involve some layering of layout objects, scripts, and calculations, all of which will add complexity to your solution.

                             

                            My advice would be to consider whether your goal is a "need" or a "want" before you go down the road of increased complexity. If it is indeed a necessity, you want to do everything you can to understand the implementation so that you can make adjustments down the road when the scope of the project changes or something breaks. Of course, as you've pointed out, the FileMaker Pro community is awesome and there are some mighty big brains around here that can assist you when you find yourself out of your current depth. Really experienced people like philmodjunk have always been willing to help me when I find myself in those situations.

                             

                            That said, I would be more than willing to share my ideas with you, if your interested. Please let me know if you need anything else. Have a great day!

                             

                            God bless,

                             

                             

                            Bill

                            1 of 1 people found this helpful
                            • 11. Re: Adding additional records from Excel and Guest viewing
                              frimages

                              It's not there. I'm logged in to the community web site using chrome browser.

                              • 12. Re: Adding additional records from Excel and Guest viewing
                                frimages

                                Think I've been a bit dumb! I've been posting from my inbox and being new to the Community I thought that was where the attached files would show.

                                I just went back to the 'Discussions' tab and there it was :-))

                                 

                                I'll look at it now.

                                1 of 1 people found this helpful
                                • 13. Re: Adding additional records from Excel and Guest viewing
                                  frimages

                                  It's evening here Bill, hope you've had a good day.

                                   

                                  I've learned a fair bit today and as we have far more PDF docs than Word I think we'll convert the Word files to PDF - wonder if I can batch them? I take your point about"need" or "want". Most of our paper archive documents are being scanned to PDF anyway so the original word files can just be archived elsewhere.

                                   

                                  The Scripts that philmodjunk sent me were very helpful.

                                   

                                  Good night

                                   

                                  Frank

                                  • 14. Re: Adding additional records from Excel and Guest viewing
                                    frimages

                                    The Scripts for the buttons were very helpful. Think I understand a little more about scripts now.

                                     

                                    Thanks again

                                    1 2 Previous Next