Filtering data and getting quantities from an Excel spreadsheet
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.