Script to extract custom field informations out of file name

Dear All,

I am a big fan of DT3 new feature of custom fields. I created two fields to put my tax rates and tax ammount for each invoice in them. Since I already put those two information into my file names, I am looking for an scripting approach to extract those two parts from my file-names and put them right into my custom fields.

my file names always have the same structure:
190811_Client_Specifications_TaxRate_TaxAmmount_ER_Price.pdf

I divide the informations with underscores, so the script should look for the information before the second last underscore and puts it into my custom field “tax” and for the information before the third-last underscore and put this information into my custom field “steuersatz”.

Is there any way, some might help my out with this little tool? My scripting abilities are very limited, I am just starting to get into this more and more.

Thanks in advance for your support.
And thanks the DT-team for setting this custom-field feature up!

What would be expected to be seen in an actual filename?

1 Like

Hey,

this is an example of an actual document:
190326_Amazon_Blurays-TheDarkKnight-ApocalypseNow_19_0265_ER_1598
19 is the tax rate of 19% for Germany, 0265 is the tax ammount of 02,65€, 1598 is the full price of 15,98€

Best and thanks.

Do you understand the concept of “class”?
If so, what class do you think a filename is?

No, i dont. But feel free to give me a spin into the right direction :slight_smile:

Okay.

class is the type of a item, like text or list.
Things like filenames have a class of strings or text (which are generally used interchangeably).

You are using an unusually uniform naming convention (which is the correct thing to do if you’re looking toward automating things; the more uniform, the better). This means we can do a little trick here:
We can process the underscores in the filename with AppleScript's text item delimiters. This allows us to split the string on the underscores, providing a list of the parts of the name separated by them.

Here is a functional code snippet, bearing in mind you’ll have to modify the custom metadata name(s) as needed. Also, I wrote this under the assumption you have created the custom metadata in Preferences > Data already. I approached it this way to show people how the metadata can be displayed as a percent or currency.

And the code (bearing in mind, I have commented it based on your example filename, so you and others can understand what’s happening)

on performSmartRule(theRecords)
	
	set oldDelimiters to AppleScript's text item delimiters
	set AppleScript's text item delimiters to "_"
	
	tell application id "DNtp"
		repeat with thisRecord in (selection as list)
			set recordName to (name of thisRecord)
			
			-- This splits the recordName on the delimiter, here set to an underscore.
			-- It says, "Ignore the underscores and report the parts they separate."
			set partsOfTheName to (text items of recordName)
			--> {"190326", "Amazon", "Blurays-TheDarkKnight-ApocalypseNow", "19", "0265", "ER", "1598.png"}
			
			-- Counting forwards
			set taxRate to (item 4 of partsOfTheName)
			--> "19"
			-- If using a Decimal Number:Percent data type, you need to convert the value to display correctly in the Custom Info inspector.
			set taxRate to (taxRate / 100)
			
			add custom meta data taxRate for "taxrate" to thisRecord
			
			--You can also count backwards, starting at -1 as the last item.
			set taxAmount to item -3 of partsOfTheName
			--> "0265"
			set taxAmount to (taxAmount / 100)
			
			add custom meta data taxAmount for "taxamount" to thisRecord
			
		end repeat
	end tell
	
	-- It's always good to reset the text item delimiters to its original value.
	set AppleScript's text item delimiters to oldDelimiters
	
end performSmartRule

I also did some basic math to make the values appear as I wanted them to in the Custom inspector.

2 Likes

Really useful little script. Thanks !

Frederiko

2 Likes

Glad to hear it! :slight_smile:

Hello,

first of all: Thanks for this script and sorry for my late answer. I became father again some weeks ago and did not find any time to deal with DT and this provided script earlier.
But now, I found some time to work with it, but I could not get this to run…

  • I assume this script needs to be handled as a smartrule or can I run this as an ordinary script from within the script folder? I have created a new smart rule and used this script as an embedded script. But it did not perform.

  • What does the line “add custom meta data taxRate for “taxrate” to thisRecord” exactly do and what do the two “taxRate” refer to (are they refering to the one identifier called taxRate?)

  • Is there anything important concerning the type of the fields? How do they need to be set?

-I also tried to create new custom metadata fields with the exact names from your example, but the script did not perform. I assume there is a misunderstanding with the correct field-identifiers. Maybe you can help me out here?

Best and many thanks.
Paul

I became father again some weeks ago

Congrats and I hope all is well with you and yours! :slight_smile: :heart:

  • Is there anything important concerning the type of the fields? How do they need to be set?

As I said, “I wrote this under the assumption you have created the custom metadata in Preferences > Data already.”
This shows both are set as Number values.

  • What does the line “add custom meta data taxRate for “taxrate” to thisRecord” exactly do and what do the two “taxRate” refer to (are they refering to the one identifier called taxRate?)
set taxRate to (item 4 of partsOfTheName)
    --> "19"

This picked up the value in the name and set it to a variable named taxRate.

-- If using a Decimal Number:Percent data type, you need to convert the value to display correctly in the Custom Info inspector.
     set taxRate to (taxRate / 100)

This does a conversion of the data in the taxRate variable, for cosmetic purposes in the Inspector.

add custom meta data taxRate for "taxrate" to thisRecord

This adds the value of the variable taxRate for the custom data defined.

Dear @BLUEFROG,
I still have to confess and say many sorries, but I couldnt get this script running yet.
I even created meta data fields with the exact names you have used, but the script did not work.
Where do you want me to put this script exactly and how do I use it?
Do I put it the embedded script of a smart rule?

Many thanks for your support,
Paul

Ahh… I bet I know what’s going on here :slight_smile:

The embedded script in my smart rule is processing selected items in the matches of the smart rule, partly due to this being a teaching edition smart rule that doesn’t have well-defined criteria in your situation.

  • Unless you’re targeting a single group with no subgroups, using Kind is Any Document can be a dangerous proposition.

  • Also, even if you are targeting a single group, this will operate on all documents without any more restricting criteria.

  • On Demand is a safer event trigger since it disallows the smart rule from acting on its own.

  • Processing the selected files was another safety measure.

1 Like

I finally made it. I was missing the actual trigger, but now I got it to work.
Many thanks for your help. Now I am playing around to find out the best way of scanning through my existing database and let the script run automatically.
Best, Paul

@BLUEFROG

And another quick question.

I should be able to use the script above to also extract the specific date (“190326”) out of my file name

190326_Amazon_Blurays-TheDarkKnight-ApocalypseNow_19_0265_ER_1598

and write it into an custom made date field “eingangsdatum”

set date to (item 1 of partsOfTheName)
add custom meta data eingangsdatum for “date” to thisRecord

But how would I tell the script that my setup of YYMMDD (190326) would be 26.03.2019 in my custom data field?

set date to (item 1 of partsOfTheName)

I wouldn’t use date as a variable name as it’s a reserved word in most instances. Use something like theDate or _date, etc.

Here’s a simple example…

set theDate to "190326"

-- Split the string into component parts by getting the characters.
set theYear to ((characters 1 thru 2) of theDate) as string
set theMonth to ((characters 3 thru 4) of theDate) as string
set theDay to ((characters 5 thru 6) of theDate) as string

-- Then string them together however you want.
set metadataDate to (theDay & "." & theMonth & ".20" & theYear) as string

-- NOTE: The "20" is not part of your original string in the filename, so here it is hardcoded. This is generally NOT a good option but it shows the string can be constructed this way.

Thanks for the setup. I applied it to the upper most script you have provided for extracting the tax from the filename, and it worked first (but extracted completely wrong dates from the filename) and now it does not work at all. I believe there is something wrong either with the add custom meta data command or a wrong datatype. I want this script to extract the date from the file name and write it into a custom data field “eingangsdatum” (type: date; format: date)

Can you give me a hint?

on performSmartRule(theRecords)

set oldDelimiters to AppleScript's text item delimiters
set AppleScript's text item delimiters to "_"

tell application id "DNtp"
	repeat with thisRecord in (selection as list)
		set recordName to (name of thisRecord)
		set partsOfTheName to (text items of recordName)
		--> {"190326", "Amazon", "Blurays-TheDarkKnight-ApocalypseNow", "19", "0265", "ER", "1598.png"}
		
		set theDate to (item 1 of partsOfTheName)
		--> "190326"
		
		set theYear to ((characters 1 thru 2) of theDate) as string
		set theMonth to ((characters 3 thru 4) of theDate) as string
		set theDay to ((characters 5 thru 6) of theDate) as string
	
		set metadataDate to (theDay & "." & theMonth & "." & ".20" & theYear) as string
		add custom meta data metadataDate for "eingangsdatum" to thisRecord
		
	end repeat
end tell
end performSmartRule

Perhaps a better way to debug a script is first to run the script as a stand-alone script (i.e. do not attach the script to smart rule), and only select one item. If a standalone script on one item works (step 1), and a selection of items also work (step 2), then you’ll know it’s about the setup in smart rule (step3).

I tested ur code using your file name in post#1 “190811_Client_Specifications_TaxRate_TaxAmmount_ER_Price.pdf”

After Step 1: The main issue is that (1) you have forgotten to reset ur text delimiter, therefore you are getting “1_9”, “0_8”, “1_1” in your code (if 190811 is the date). Always remember to restore the delimiter back to Apple’s default because the change is persistent. If you haven’t restored the oldDelimiters, when script put “Characters 1” and Characters 2" together as string, “_” will be added between the characters.(2) Since you are adding manipulation to “theYear”, “theMonth”, “theDay”, you need to move the two lines of “set oldDelimiters…” to within the repeat loop. (3) You have added two “.” in this line

 & "." & ".20" 

After moving and adding the code (see below code), step 1 to step 2 works. Then I paste the code to a smart rule and Step 3 also works.

Perhaps: If this still doesn’t work, try adding “md” to your custom metadata name, i.e. using “mdeingangsdatum” instead of “deingangsdatum”. As far as I am aware, the exact name of cmd field in DT script is always md+ the identifier name that you see in Preference->data. I am not entirely sure whether “md” is necessary…

Good luck.

tell application id "DNtp"
	repeat with thisRecord in (selection as list)
		set recordName to (name of thisRecord)
		set oldDelimiters to AppleScript's text item delimiters
		set AppleScript's text item delimiters to "_"
		
		set partsOfTheName to (text items of recordName)
		-- you need to add this line
		set AppleScript's text item delimiters to oldDelimiters
		set theDate to (item 1 of partsOfTheName)
		
		set theYear to (characters 1 thru 2 of theDate) as string
		set theMonth to (characters 3 thru 4 of theDate) as string
		set theDay to (characters 5 thru 6 of theDate) as string
		set metadataDate to (theDay & "." & theMonth & "." & "20" & theYear) as string
		add custom meta data metadataDate for "eingangsdatum" to thisRecord
		
	end repeat
	
end tell


The essence is correct, but this line…
set metadataDate to (theDay & "." & theMonth & "." & "20" & theYear) as string

… could be…
set metadataDate to (theDay & "." & theMonth & ".20" & theYear) as string

1 Like

Hello @ngan,
thanks for this help and the detailed explanation. I just getting started with appleScript and those details help a lot to understand the basics behind it.

I tested the script in AppleScript Editor and it worked just fine with your code provided, even without the md before the identifier. But as soon as I copy the code into the smart rule embedded script field, I come back to my main problem:

A complete different date is being transfered into the metadata field. When using the date of 190811, the date being transferred into is 31.12. 200 with an exact empty field before the 2 of the year. It seems that there is still an issue when pasting the code into the smart rule script.

I am kindly confused, since the applescript works properly in the editor, but the smart rule seem to be the problem here.

Any ideas, what might be the issue here?

Perhaps u can capture and show ur smart rule here. Might be just the setting of the rule.
I don’t use smart rule that often, but I’m sure that u’ll be getting advice here…

PS. IMHO, u are attempting a task that involves multiple steps of which you are not yet very familiar with (new DT, new script…) and perhaps a better way is to start with the simplest possible setup in each stage. In the case of smart rule, when I test the script, I tested with this simple rule (see below). the rule only involves dragging a file on to the rule itself (On Demand), and it works. So, I know that the rule can trigger the script. The rest is about whether you are setting the predicates and conditions of the rule correctly.