I'm a filemaker newbie and have been successful making simple solutions for basic data entry and reporting. I have an actual problem I'd like to tackle that is most likely above my pay grade. Figure it will serve as a nice long frustrating way learn more about filemaker.
We have two applications that output product ordered totals, one is a sql server db, the other visual fox pro. Connecting directly to them is not an option due to vendors but I can export the reports to csv files. Ideally I would like to import the files into filemaker pro and have it combine them and produce a report. It would help cut down on mistakes as people transcribe these #'s daily and perform math on their own.
One issue each database uses it's own item_id's and names due to limitations or auto naming conventions. So it seems like filemaker would need to import the data, associate the imported item and it's id with a separate db to rename the import. Basically, the final output would look like a report like the table below.
I'm not sure whether filemaker is overkill for this, would excel be an easier less involved method? I'm having trouble getting my head around what the table structure would look like.
I assume a table for master item list of all the items with names use for the report. Each item would have it's own unique ID and on import there would be some method (calculation?) to match the corresponding id from csv import.
It gets difficult for me to understand the structure that follows after the import. If the imported items get renamed based on lookup match of the first table would each csv import be put in a separate table? And then another table to pull the imported data together for the combined report? Filemaker would also have to know which csv file corresponded to the appropriate column i.e. wholesale, retail, house. Thanks in advance for any help.