Auto import with dynamic name

[Felicia]

Hello all,

This is my first post.

Each year we do inventory in a retail location.

We have 25 scanners that collect the barcodes that are counted and they include what tag/section number they have scanned.

We auto send all the scanner files to 1 computer in a specific folder on that computer. However, they are tagged by scanner number, store number, day of the week and military time.

I'm looking to find a way that filemaker can scan that import files from that folder every 10 minutes, import the file there and put in the file name into a specific field, append the end of the file to "imported" and move somewhere else (or at least not import again).

Thoughts? I can have it renamed and/or moved with an apple script but not sure how to get filemaker to check the folder to pull in different file names.

 

Thank you in advance.

 

Felicia

Edited May 29, 2017 by Felicia

[MikeKD]

Hi there, and welcome!

Hopefully, some of the smarter folks will jump in to help soon; I'm not exactly an advanced user!

I'm sure what you want is achievable, but it would be useful to know:

What format is the file? Excel?

Is there some kind of unique identifier for all the records on the file?

Is the location of the file always the same?

Does the file contain all the scans or only the last batch?

[Felicia]

Hello, thank you. I hope someone jumps on too.

The file is a .txt.

Sample file attached, it's just a barcode, comma, quantity.

Picture of import attached. In a perfect world, the file name would also import but I may have to update that later with a variable, that will be ok.

There are lots of script steps that would come after this import. This is the only part of the whole process that requires human interaction at the moment.

The location will always be the same.

I can have the folder with the scans have ALL or remove them as they import using an applescript.

 

Thank you.

 

04_02_01_1203.txt

[MikeKD]

Hi Felicia,

In the text file, is the 1st number the unique identifier?

Quote

817894017430,1
817894014088,2
817894017393,6
etc

Do you know what numbers are going to come up - do you know which parts are going to be scanned? (in which case, you can refer to a table of UI with their part name etc.)

You've obviously worked out how to import your CSV - is it running the script every 10 mins that's giving you a headache?

[Felicia]

Hello.

The unique identifier would be the file name.

We have 500,000 skus so any one of those can be in the file. They aren't even all the same length.

I do not know what parts are going to be scanned. Just which department in the store we're scanning that day.

I will want it to run every 10 min, I haven't done that part yet. First I need to find a way to import any file that is in the folder, regardless of the name of the file.

[comment]

30 minutes ago, Felicia said:

First I need to find a way to import any file that is in the folder, regardless of the name of the file.

Actually, that is the easy part here:
http://www.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Fimporting-folder.html%23

[MikeKD]

It's usually not a good idea to have the name as a unique identifier. Isn't the SKU the unique identifier? If you've got a record of all the SKUs do you also know what item they refer to, or is this all part of stock taking and you don't know what the item is until it's been scanned? (in which case you'll be creating a new item record based on the scan?). I don't think the irregular length of the SKU is a problem.

I'm not sure how to get the file path of a new file in a folder - don't know if FM can do that natively - will probably have to use AppleScript for that bit; I can't see how FM would even know it's there. I think you're going to have to go with your moving or renaming the file with AppleScript idea (you can "Perform Applescript" from within FM)

I've had a quick shifty about scheduling scripts and I can't see an easy way unless you've got FileMaker Server..

Yay; the cavalry has arrived! Thanks comment!

[comment]

8 minutes ago, Felicia said:

Do you know if it works in FMP15 too?

Oh, yes: it works in all versions since version 6, if I am not mistaken. For v.15, you just need to change the /16/ in the URL to /15/ to find out for sure.

[Felicia]

Thank you! This is great.

I might be able to figure this out but maybe you have an easy answer.

So now it brings the entire text file into the first record.

So I get the following in the first field.

610393001425,1
610393071503,2
000399576483,1
610393072104,1
610393072142,1
000399332089,2
000399492882,2
000399492868,1
610393001463,3
610393060231,2
610393072074,1
000399565111,3
000399572294,3
000399390706,2
610393001616,2
000399578951,2
610393001555,1

 

 

I need to pull this out to be 17 individual records.

Thanks!

[MikeKD]

Looking back at your 1st post, your import dialogue looked right - you had it set to create new records from each line.

Is that what you want to happen? What if one number has come up previously; do you want to create a new record or update the previous one?

Which I suppose brings up the question - what are these records? Are the items or are they something else - a product line?

Cheers!

[comment]

19 minutes ago, Felicia said:

it brings the entire text file into the first record.

Ah, sorry. That's not going to work for you then - unless you want to make your script parse each imported file into individual records (preferably in another table)*.

Otherwise you will have to import each file individually. Although technically it is possible to accomplish this using Filemaker's native methods, I would suggest you use either AppleScript or a plugin (such as the free Base Elements) to handle the selection of files for import and especially their deletion afterwards.

 

--
(*) It's not as difficult as it may sound.

Edited May 30, 2017 by comment

[MikeKD]

Could you export that field as a text file and then import it as a CSV?

[Felicia]

3 minutes ago, MikeKD said:

Could you export that field as a text file and then import it as a CSV?

I was thinking just this actually, going to try tomorrow.

Thank you all for the quick replies, I really apprecaite it.

[comment]

8 minutes ago, MikeKD said:

Could you export that field as a text file and then import it as a CSV?

It already was a CSV file before it was imported, so I think that would be kinda going around the block. What you could do is import only the file path, then loop over the imported records and use the file path to import the original file. But you'll still have the issue of deleting the files (or at least moving them out of the designated folder) afterwards - and as I hinted before, I would prefer to use a 3rd party tool for this. And if you're going to use a 3rd party tool, you might just as well have it do all the file handling.

 

Edited May 30, 2017 by comment

[MikeKD]

I'm happy to defer to comment - he knows a hell of a lot more than me!!

[Felicia]

5 minutes ago, comment said:

It already was a CSV file before it was imported, so I think that would be kinda going around the block. What you could do is import only the file path, then loop over the imported records and use the file path to import the original file. But you'll still have the issue of deleting the files (or at least moving them out of the designated folder) afterwards - and as I hinted before, I would prefer to use a 3rd party tool for this. And if you're going to use a 3rd party tool, you might just as well have it do all the file handling.

 

 

 

Even better, thank you.

I can move them with an applescript, no problem them.

I think this solves everything.

I'll check out Base Elements too.

Edited May 30, 2017 by Felicia
add info

[comment]

I should add one more option: if you do this in two steps (import the file paths into a Files table, then import the files themselves into an "InventoryCounts" table), you could set the FilePath or the FileName field in the Files table to validate as unique, validate always. That would prevent the creation of duplicates even if you leave the files in the source folder after importing them. 

Caveat: I haven't actually tested this as a whole.

 

 

Edited May 30, 2017 by comment

[ggt667]

What is the issue at hand? I always do my imports using a $tmp variable works for XML for sure as I use this n times pr day. May also work for the file type of your liking.

Set Variable[$tmp, Get( DocumentPath ) & table::filename]
Import[$tmp]

 

Edited June 20, 2017 by ggt667