Left ( YourFilenameFieldwithExtension ; Position ( YourFilenameFieldwithExtension; "." ; Length ( YourFilenameFieldwithExtension ) ; - 1 ) - 1 )
will remove the file extension.
I've created the script but it doesn't work. I'm not sure how I'm supposed to use "withExtension" - this is what I did below, can you please make sure this is correct? My table::field name is "Picture DB::A_VFX Shot #" in the example below. And I am matching fields when I import the records in a folder.
Attached is a screen grab of my script set up too. And please note I'm importing .jpeg extensioned files.Left ( Picture DB::A_VFX Shot #; Position ( Picture DB::A_VFX Shot #; "." ; Length ( Picture DB::A_VFX Shot # ) ; - 1 ) - 1 )
What you need are two fields that correctly match filenames in every letter. SInce the imported files will include the file name with the file extension, you need a match field in your existing data that also has the filename with extension.
If all of your files have the same file extension a simple Replace Field Contents operation can append that extension to the filename in your existing field in every record in your table. If the extension varies, this becomes more problematic but there are ways to deal with that so let me know if this is the case.
Once you have your filename updated to include the file extension, you can use it as your match field for this import.
If you need a field with the filename and no fileextension, you have two options that both use the expression that I posted earlier:
1) Use replace field contents with this expression to remove the added file extension from the field.
2) Add a calculation field to your database that uses this expression to remove the extension and use it in every case where you need the filename without the extension.
The replace field contents operations can be performed manually or in a script.
Ok I think I can figure this out. Could you possibly help me with the replace field calculation? Every time I try it the correct result doesn't happen.
All extensions are "jpeg" and the original names are all the same length - 7 characters long.
Let's say your table is named "Table" and the file is named "FileName".
Then this expression can be used with the calculated result to append .jpg to the end of the text in FileName for each record:
Table::FileName & ".Jpg"
If there is the slightest chance that .Jpg is already in place for some records, use:
If ( Not ( Patterncount ( Table::FileName ; ".Jpg" ) ; Table::FileName & ".Jpg" ; Table::FileName )
Just substitute your table occurrence and field names for mine.
That works great. Thanks so much for your help. Very much appreciate it!