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.
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.
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().
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.
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?
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.
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.