7 Replies Latest reply on Apr 7, 2015 12:41 PM by philmodjunk

    Filtering data and getting quantities from an Excel spreadsheet

    JeffBroderick

      Title

      Filtering data and getting quantities from an Excel spreadsheet

      Post

       

      Hello,

       

      I use a FileMaker file to import information from two different Excel worksheets and compare a report with actual barcode scanned counts.

      It is very simple with only two tables.  

      Table one = HardwareMatching

      Table two = QuantityScanned

      HardwareMatching is used to import a POS system report (Excel) with qty and item number which is an inventory report.

      QuantityScanned imports from a spreadsheet where individual scanned items have been aggregated and computed in the spreadsheet to create a  qty scanned of each item number.

      With a layout related to HardwareMatching, I have the fields:

      HardwareMatching::Qty and HardwareMatching::Item and on that layout, I have also put QuantityScanned::Qty and QuantityScanned::Item.  

      A calculation field shows which quantities match, which are missing and which are over in terms of qty and items matching each other.

      All this leads to my actual question: in my Excel sheet, I use Advanced Filter to separate unique values and then have to use a Countif formula to count the actual individual item numbers to actually apply quantities to the scanned items.

      I'm wondering what the best method in FileMaker will be to bring in a single column of items in this spreadsheet of scanned individual items and have FileMaker count them up so I can have qty matched with items.

      I can then let my FileMaker file do what it already does very well - match the two tables and give me the results.

        • 1. Re: Filtering data and getting quantities from an Excel spreadsheet
          philmodjunk

          I'm not sure that I understand all the details of your description of these two tables.

          Are you linking records between the two tables by a bar code that uniquely identifies each inventory item? (and only by that bar code?)

          Is there only one such record for each unique bar code in hardwarematching?

          and there are multiple records for each bar code in QuantityScanned?

          If so, then a calculation field defined in hardwarematching:

          Sum ( QuantityScanned::Qty )

          will provide the total number of items in QuantityScanned for a given record in hardwarematching.

          • 2. Re: Filtering data and getting quantities from an Excel spreadsheet
            JeffBroderick

            Hi Phil, 

            Actually, the table descriptions might have been a distraction. The real question is how to import a spreadsheet, using my picture as an example, and have the data create a record for each item with a total quantity.

            Record 1       Item: widget     qty: 4

            Record 2       Item: apple       qty: 5

            Record 3       Item: bird          qty: 2

            Record 4       Item: guitar       qty: 1

            Once I get the data in this above format, the existing database file already does what I need. Just trying to automate the counting of the individually scanned items.  The barcode aspect only allowed the scanning of the items into a document, which was then copy/pasted into the spreadsheet. Sorry to be unclear.

            • 3. Re: Filtering data and getting quantities from an Excel spreadsheet
              philmodjunk

              Are you aware that you can use a bar code scanner with FileMaker and scan data into a field in FileMaker? that might eliminate a lot of steps here if such is possible for you.

              Ok, I see the problem with your spreadsheet, but now does one of your two tables have the same design as shown for this spread sheet?

              My original answer assumed this kind of data in QuantityScanned. The only change from my original description is that you can use Count ( QuantityScanned::FieldForColumnA) instead of Sum().

              • 4. Re: Filtering data and getting quantities from an Excel spreadsheet
                JeffBroderick

                 

                HI Phil,

                I'm aware you can do barcodes with FileMaker but unfortunately, there are hundreds  of items - some hanging on hooks and close quarters and the fastest method is with a Tricoder (a small portable self contained laser scanner) which goes much faster and then stores all the individual scans, hence the upload to .rtf and then copy/paste to Excel.

                I don't currently have a table that uses the approach of each scanned item (spreadsheet row) is a record.  

                So it sounds like you're suggesting that I just bring each item in as a record in a different table (or possibly QuantityScanned::Item) and then use a calculation or summary field to get the total count of each item.  

                Once I have the count by item number, what's the best way to get those figures back into my comparison layout?  Would I use a Set Field with the total count to my QuantityScanned::Qty or just use the results of the Count(QuantityScanned::FieldForColumnA) in lieu of the QuantityScanned::Qty field which I used to manually calculate?

                Otherwise, I'm not sure if I just scan those items into QuantityScanned::Item that even though I get a count of each item to put into QuantityScanned::Qty, it seems I would have a bunch of duplicates that would need to be culled?  I guess I will try seeing what kind of results I get with the Count() function first.

                 

                 

                • 5. Re: Filtering data and getting quantities from an Excel spreadsheet
                  philmodjunk

                  In most, but not all cases, I'd just use the calculated value. You will have many duplicates in your table of scanned data, but this is coalesced into a single aggregate value via the relationship on your other layout. I don't see how that will result in any duplicates. You can even import the scanned data twice, once into a field where you have specified "unique values", validated always and once into the table of scanned data as we have discussed here. In the table with this validation option, duplicates will be automatically removed.

                  It's also possible to use a summary field in the table of raw scanned data that counts your records and a list view layout with a sub summary layout part "when sorted by" your bar code and with the body layout part removed to get a list where each item only appears once.

                  Have you considered that you might be able to import directly from the rtf file into FileMaker and save a step?

                  • 6. Re: Filtering data and getting quantities from an Excel spreadsheet
                    JeffBroderick

                    I will try that later.  Guess I never tried importing from a document.  I think I have a couple of ways to try now and get this wrapped up.  

                    Awesome tip on duplicate elimination on "unique values".  Guess I never thought of that on an import, always thought of that as prevention for bad data entry - that's a powerful feature for importing.

                    I'm looking forward to FileMaker making yet another time consuming data crunching process into something that takes seconds.

                    • 7. Re: Filtering data and getting quantities from an Excel spreadsheet
                      philmodjunk

                      Since it appears that you have a simple text file with each row separated by returns, a csv or tab import may work. You might have to change the file extension from rtf to txt.