Best Solution?

[sicSRT8]

I have another complex issue that I would like to figure out a solution to. So here goes... I have a reporting DB for our daily production reports.

We have an excel spread sheet updated daily based on current inventory. Right now we have a script to import the .xls into FM and delete previous invenotry records. This is so we can select the item and the updated qty for that day. So the old files are wiped from system and new one imported.

The obvious issue is that if the reports or inventory is not entered when its supposed to be it creates more work to fix the issue to be able to input the report.

What I want it to do is be able to select the inventory based on the production date and not the date being entered. So If the report wasn't entered in yesterday but today's inventory has been updated I want to be able to see the inventory from the production date.

So I guess my question is how can you date inventory imports when the qty available adjusts daily.

I know how to set relationships but not sure on the dating issues on imported items. Each Item has a specific serial number.

If this wasn't clear I'm sorry let me know and I will explain further on anything I missed.

[fseipel]

First, if the Excel export doesn't already do this, I recommend creating a pushbutton to trigger a VBA (Visual Basic for Application) macro in Excel. VBA runs on Macs or Windows. This script will:

Take the currently selected block of inventory cells, or all cells in current active worksheet, or the most recent (as appropriate), and write out a text file with the data, then trigger a filemaker script to import the data automatically, without any manual imports required.

The filemaker script triggered by the VBA macro, will, in turn, read this file into a new 'temporary' table, then check the date(s) against the most recent date in the 'main' inventory database, either discarding the new imported data (if what's in Filemaker DB is newer), or deleting old data and replacing it with new, and copying said data from new table to main database table.

The script might also display a message summarizing what actions it took.

Alternately, have users enter all data into Filemaker; when they need Excel, they can export the data, or access it via ODBC from within Excel.

I have generated a solution for laboratory data from gas chromatographs which worked on Macs or PC's several years ago with this technique and it worked quite well for integrating Filemaker with Excel.

Using VBA, you can output the data in a format that is most convenient to import back into Filemaker, for instance, repeating the date on each line for each item, making first line of file a command or description of the data or username from whence it came, etc. I'd just use a fixed name for the file, e.g. C:EXCEL_FM_IMPORT.txt

E-Mail me if you'd like a quote on this project.

[sicSRT8]

That cant be the best solution for this issue. I dont need all that. I just need dates set to the records...

[fseipel]

Perhaps instead you should simply change the import field mapping so that you 'match records based on this field' (equal sign in Import Field Mapping Box), and match the serial numbers. Also click Import Action = Update Existing Records in Found set OR Update matching records in found set but NOT Add new records (so it updates the existing records).

If you import both the quantity and the date, this should do what you want, if I understand it correctly.

NOTE: Assuming spreadsheet has a separate row for each item, you will need to create a new column, called Date, and set all values in it, say, to =NOW() [current timestamp]. Then hide that column. When you export, each row will have the current date.

If you need two dates, use two columns (e.g. date inventory taken/date inventory imported into FM). Once these dates are in FM, the scripts should be able to generate the necessary reports and alert if inventory hasn't been taken.

This will not be as elegant as the previous recommendation which automates the Filemaker import but it is easier to code.