10 Replies Latest reply on May 18, 2012 1:29 PM by thewoz

    Little help for a newb

    kwrigh

      I am new to Filemaker Pro. Could someone help me in creating one report with a common columnar format from multiple excel documents?

       

      I have several excel documents that all have a common columnar layout, however some of them have subtotals, totals, blanks etc. in the rows. I would like to import each of these files, maintaining the column layout but eliminate all the sub total, total and blank rows and create one combined report. I have done some of the training, but have not seen how to do this exactly and don't have much time. Thanks to anyone for help.

       

      Kim

        • 1. Re: Little help for a newb
          wrwaugh

          With the little information you provided it is hard to respond with clarity.  I would offer the following.  If all the data in the colums is the same, then you can make a Filemaker database which has a table with appropriate fields to capture the informatio from the excel tables. 

           

          Now you can open each excel sheet with FileMaker which will result in making new FIleMaker database files (converted from Excel) wherein each column will be a field and each row of the Excel table will be a record in FileMaker. 

           

          Going to your original FileMaker file you can import the resords from the converted Excel FileMaker files.  One file after another.

           

          Now all the data is in one FileMaker file and you can create the necessary calculation and summary fields to create the report you need.

          • 2. Re: Little help for a newb
            atlascott

            It sounds like you have multiple Excel spreaheets with similar data types and you want all of them to to go into one database, and then do reporting and summarizing on all of the data.  If so, here are the steps you need to do.

             

            1.  Create a new database.

            2. Go to Manage->Database.

            3.  Name your Table whatever you'd like.

            4.  Define Fields in the Table.  Generally, name them what you column headings are named in your excel spreadsheets.

            5.  Go to File - Import Records - File

            6.  Choose the first Excel File.

            7.  In the Import dialog window, move the columns of data in the Excel spreadshhet around so that the table you nameed correspond to the correct data.  For example, if you have a Excel column of data with a header called "product price" then map it to your Filemaker field called "productPrice".  This makes the data in the spreadsheet go into the right data tables in Filemaker. You are telling Filemaker where to put the data.  Once this is done, import the data.

            8.  Repeat steps 5-7 untl all of your data from the spreadsheets is in Filemaker.  In the table view, Filemaker should look alot like a spreadsheet, and it should now have all your data in the one database.

             

            Now, you can make summary fields and generate reports which wil average or count or whatever you want with the combined data.

            The Help feautre in Filemaker tells you how to make th4ese fields and how to generate summary reports.

             

            Hope this helps.  Good luck!

             

            Scott

            • 3. Re: Little help for a newb
              kwrigh

              Thanks for the help Scott.  Got the database created using your excellent instructions., but am hitting a problem getting the files combined. Each time I import the next file, it over-writes the first.  What am I doing wrong?  Then next thing I would like to try after I get it all working manually is to try to write a script to do it automatically.  Is that difficult?

              • 4. Re: Little help for a newb
                karendweaver

                Are you sure it is overwriting? When you import a file, FileMaker automatically replaces the current found set with the imported records.  So check the status area and make sure of the total number of records, not just the found set. - is it the same (overwritten) or are there new records in the database? 

                 

                If you are really overwriting, just set the import options to " Add New" instead of updating current records.  But since "New" is the default, I suspect you are getting confused by the found set.

                 

                A script is not difficult.  When you get the manual system working, write down all the steps you took, and then create a script with those same steps.  That should get you pretty close, then you can post some more questions.

                 

                Karen

                • 5. Re: Little help for a newb
                  atlascott

                  When you import more than one file, Filemaker defaults to showing the newly imported data as the found set.  It isn't overwriting the data,m it is just showing you only the newly imported records.

                   

                  Click the "Show All" button on the menu and you will see ALLLLL of your data in the view.  See?  Filemaker DID import all your data!

                   

                  If this isn't working, let me know. But I think your data is in there.  Also, look at the Records display which shows the number of records.  It should show a number which is the sum of the records in all the Excel spreadsheets.

                   

                  There is an Import script step, so there is no reason why you couldn't write a script to do it.

                  • 6. Re: Little help for a newb
                    kwrigh

                    Thanks so much for the help. You were right. I did have all the data in there.

                     

                    Next question.  I am trying to make a report with a common format and eventually automate it so that I can import several excel files into the database and create one report with a common format quickly. The excel files that I have all have a common field layout, but the record layout is all over the place. Several of them have extra rows, subtotals etc.   I now have the database set up with the correct fields, but I can't see how to eliminate all the rows (records) that I don't want?  Also, there are several empty rows (records) between each file that I have imported.  Do you know why that is?

                    1 of 1 people found this helpful
                    • 7. Re: Little help for a newb
                      karendweaver

                      Sometimes if someone has entered info in a row and then deleted it, on import, FM counts it as a record.

                       

                      In order to eliminate the empty rows and the unwanted rows, you can add additional steps to your script.  First, you will need to determine what cell or cells contains data that either flags it as a good row or a bad row.  Think about how you would delete those rows manually.  First you might do a search for no data in any field (the = sign in the search box will return records where a field is empty - so one find might be = in every field)  Then you delete the found set.

                       

                      Next do a search for the fields with extraneous information - you might either search for records that don't have data in important fields, using the equal sign, or do a seach for specific  words "total" or "subtotal" - you get the idea.  The important thing here is to do a manual search and delete FIRST, with EVERY spreadsheet, so that you can verify they are formatted properly and you don't find any records that should not be deleted.  Then delete the found set.

                       

                      You will want to add some error trapping before you delete - add a script step to check to see how many records are being deleted - make sure you do not have a find error that returns too many records.  You don't want to delete all records!  You can capture the total number of records or the number imported in a variable, then compare that number to the number of blanks or problem records that will be deleted. 

                       

                      Another method I have used is to require users to use a template file for the import.  They can copy and paste the Excel rows into the template and import from there, and you could reject imports that are not using the properly named import file.  Depends on how critical the data is...

                       

                      Hope that helps!

                       

                      Karen

                      1 of 1 people found this helpful
                      • 8. Re: Little help for a newb
                        atlascott

                        Unfortunately, there is really no way to verbally describe in a brief way how the layout tools in Filemaker work.

                         

                        So, I cannot give you step by step assistance with that.

                         

                        I would very strongly suggest spending a bit of time learning how to make a layout, and learning about the different display modes.

                         

                        Literally, in an hour or two of learning, you'll be close to mastering the layout tools, and then, creating exactly the layout and reports you want will be very clear and easy for you.

                         

                        You can catch some free videos on YouTube, or join VTC for a month and watch a great video introduction and instruction on layouts, or get the "Missing Manual" book.

                         

                        Filemaker has, essentially, the ability to instantly create a new Layout (as many differeint ones you want), and then you drag and drop whatever fields of data you want on the report.  If you are using FM12, you can choose a Theme and it automatically stylizes your data very nicely.

                         

                        As far a scripting goes, it is always more complicated that it first appears, in my opinion. Doing training on scripting is a really, really good idea if you are doing any kind of more complicated script - yours is not super complicated, but it would help you.  You'd benefit alot from the training in creating your script.  It would save you lots of trial and error.

                         

                        For me, learning Filemaker was learning and then doing in equal parts.  Unfortunately, I do not think the "learning" part can be skipped, and by following a "learning by doing" model, you are really missing out on the fastest way to attain your goal and missing out on learning about other features and abilities FM has.

                         

                        Part of your frustration with getting detailed help on the forums will include not understanding terminology that experienced developers use.  Really, no substitute for rolling up your sleeve and watching training vids or doing some reading.

                         

                        Good luck!

                        • 9. Re: Little help for a newb
                          KenNewell

                          I know this is a little late since the file has all ready been created.  FileMaker can read the excel file and CREATE the file for you no need to create the table.  Great way to kick start your DB without having to create the table from scratch.  Haven't done it for a while but I think a simple drag and drop of the excel file into FM works rather nicely.

                          • 10. Re: Little help for a newb
                            thewoz

                            You can deselect fields that you don't want to import in the Import Field Mapping dialog box The best way to do this is to make sure that the field names in your Filemaker tables have the same names as those in your Excel files. Then you can check the "Arrange by Matching Field Names" option to get the correct import sets. I generally create a new table for each Excel file format by importing the first Excel file with a new format into my database. When doing this initial import you need to specify that the first row contains field names and that you want to create a new table from the imported file. In this way, Filemaker will create fields with the same names and data types. There are naming convention issues that can crop up here if your Excel file uses certain characters in the field names. Once the table is set up you set up an import script to bring new data in. You can also eliminate summary data and blank rows that were in the Excel file by searching for blank fields. I've attached two Excel files and a FileMaker database (FileMaker 10) that you can use to import them as an example. Take a look at the scripts and the import dialog box to see the correct settings. Note that I do not import all of the fields and that I have a calculated field that produces a value from one of the imported fields. You can easily create a master script to run the first two scripts. You can also change the import and delete records script steps to "Perform without dialog" once everything is working correctly. Good luck.