Trying to export number of words translated...

Funny you should mention that. I just wanted to run some reports on how many words we translated and proofread in specific language combinations and could not get the info I wanted. I went to Projects/Jobs and then searched for language combination for ready translation jobs, but the only volume information I could select from the available columns/filters was Project/Task/Volume, which is the text field in the top Order card of the Smart Project. This, however, is only a rough estimate mainly used for preliminary jobs announced by customers in advance. The accurate word count or line count information should be taken from the Finance card, which is based on the CAT analysis files. There should be access to both Total Word Count and Weighted Word Count from the job payables section, which is the relevant data.

What is the workaround to get to that information? Is there a way to get to this via Vendors/Jobs or Reports? If so, how?

Could you please add these columns/filters to the Smart Views for future use?

BTW, the same accurate volume information is missing for the Receivables (in our case: line count).

You always seem to ask the same question that I'm trying to ask, but you do it in a much more thoughtful and articulate way. I'm just going to keep saying, "what Sancho said"...

You are too kind. I try to be articulate, but usually end up being too wordy  ;o)

It has been said, "I would have written a shorter letter, but I did not have the time."

I use virtual columns that extracts data from task receivables. I use a separate virtual column for weighted and total word counts. Totals is better for marketing purposes, while weighted is used for internal calculations :)

I also have the same virtual columns for jobs.

However, it has some limitations: 

- You cannot sum, filter and sort by virtual columns in views. But you can export them to CSV and analyze the data in Excel, Power BI etc. 

- Some clients pay by character count and/or hours. The virtual column would report 0 words for these tasks.

- I don't know if my virtual columns work with Smart projects - we stayed with Classic projects.

In a separate reply, I share the code here - feel free to use it.

Job word count (total)

#set( $tmpwordcount = 0.0)
#foreach( $payable in ${utils.unwrap($this).getAllPayables()} )
    #if( $payable.getCalculationUnit().getName() == "source word")
        #set( $tmpwordcount = $tmpwordcount + $payable.getTotalQuantity())
    #end
#end
$tmpwordcount

Job word count (weighted)

#set( $tmpwordcount = 0.0)
#foreach( $payable in ${utils.unwrap($this).getAllPayables()} )
    #if( $payable.getCalculationUnit().getName() == "source word")
        #set( $tmpwordcount = $tmpwordcount + $payable.getQuantity())
    #end
#end
$tmpwordcount

Task word count (total)

#set( $tmpwordcount = 0.0)
#set($taskFinance = ${utils.unwrap($this).getProjectFinance()})
#foreach( $receivable in $taskFinance.getCatReceivables() )
    #if($receivable.getCalculationUnit().getName() == "source word")
        #set( $tmpwordcount = $tmpwordcount + $receivable.getTotalQuantity())
    #end
#end
#foreach( $receivable in $taskFinance.getReceivables() )
    #if($receivable.getCalculationUnit().getName() == "source word")
        #set( $tmpwordcount = $tmpwordcount + $receivable.getQuantity())
    #end
#end
$tmpwordcount

Task word count (weighted)

#set( $tmpwordcount = 0.0)
#set($taskFinance = ${utils.unwrap($this).getProjectFinance()})
#foreach( $receivable in $taskFinance.getCatReceivables() )
    #if($receivable.getCalculationUnit().getName() == "source word")
        #set( $tmpwordcount = $tmpwordcount + $receivable.getWeightedQuantity())
    #end
#end
#foreach( $receivable in $taskFinance.getReceivables() )
    #if($receivable.getCalculationUnit().getName() == "source word")
        #set( $tmpwordcount = $tmpwordcount + $receivable.getQuantity())
    #end
#end
$tmpwordcount

Many thanks for sharing the code for this Laszlo, very much appreciated. Do you think the code can be modified to display information on wordcount not only on job/task but also on project level?

Hi Tomas, 

Sure! It's not a big deal, will post it sooner or later (hopefully tomorrow at latest).

Hi Tomas,

Sorry, it was later, not sooner :(

Here is the code for Project weighted word count:

#set( $tmpwordcount = 0.0)
#foreach( $task in ${utils.unwrap($this).getTasks()})
    #set($taskFinance = $task.getProjectFinance())
    #foreach( $receivable in $taskFinance.getCatReceivables() )
        #if($receivable.getCalculationUnit().getId() == 1)
            #set( $tmpwordcount = $tmpwordcount + $receivable.getWeightedQuantity())
        #end
    #end
    #foreach( $receivable in $taskFinance.getReceivables() )
        #if($receivable.getCalculationUnit().getId() == 1)
            #set( $tmpwordcount = $tmpwordcount + $receivable.getQuantity())
        #end
    #end
#end
$tmpwordcount

And for Project total word count:

#set( $tmpwordcount = 0.0)
#foreach( $task in ${utils.unwrap($this).getTasks()})
    #set($taskFinance = $task.getProjectFinance())
    #foreach( $receivable in $taskFinance.getCatReceivables() )
        #if($receivable.getCalculationUnit().getId() == 1)
            #set( $tmpwordcount = $tmpwordcount + $receivable.getTotalQuantity())
        #end
    #end
    #foreach( $receivable in $taskFinance.getReceivables() )
        #if($receivable.getCalculationUnit().getId() == 1)
            #set( $tmpwordcount = $tmpwordcount + $receivable.getQuantity())
        #end
    #end
#end
$tmpwordcount

Note that I've implemented Denis's suggestion about using the calculation unit ID instead of name so that it works on non-English XTRFs :)

Have fun!

(and thanks for the suggestion - I introduced it into our system, as well)

Hi Laszlo,

thanks so much for spending your time on writing the code and sharing it with all of us! It's really of great help!

Thanks for this post guys. We were offered the virtual column option by XTRF but had yet to explore this option so this is very useful.

For now we use a 'Number' Report based on Number of Client Calculation Units and filter by client, deadline or invoice date, and it gives us total source word counts, hourly rates applied etc. I believe it is a simple report and just gives total source counts...maybe there are further options but this is what we have used for now.

Laszlo, thanks for sharing the code. Could you advise how to implement it whitin the system?
I set them up within the Virtual columns with class name Task, Type: Plain however I can"t seem to find the added column(s) when editing the views in tasks module.

Sure, Michal! I post my Settings -> Customization -> Virtual column screen here:

And after that, I could add it as a column in any view created for tasks:

Note that you CANNOT add it as a filter in the upper part of the view creation dialog :( You won't see it when you click the Add filter button.

Earlier, I tried to push XTRF to enable filtering for virtual columns, but they told me it's risky and takes a lot of computing resources. And they are right: anyone can code virtual columns, and might crash the system in no time.

Huge round of applause to Laszlo!!!

Thank you very much for being willing to share. What an asset to the User Echo community.

And just one note for all non-programmers among us (might be too basic for some, so forgive me):

Make sure to adapt the Calculation Unit name from "source word" to whatever you have defined in your system. In our case, we use German terms for the Calculation Units. Therefore, the code looks like this (note the yellow highlights):

Maybe an even cleaner option would be to use .getId() in stead of .getName() as the name can change if you use different languages.

The ID of a Calculation Unit can be found if you go to Settings --> System Values: Advanced --> Calculation Units. Select a calculation unit and then in the url it states id=xx.

It can then be used like this: $payable.getCalculationUnit().getId() == 1

Good idea, reduces the error potential. Thank you, Dennis!

Thanks much :) We use XTRF in English only, but it makes my virtual columns truly international!

Thank you Laszio! I'm going to give this a try later today. Thanks!!!!

I tried it and it worked like a charm. Thanks!

Great info Laszlo ! I implemented both and they are working great.

HI! 

This is just what I was looking for! Thanks!

However, I'm wondering if it is case sensitive, as i'm not returning any results from task/job/project level. 

Another question for the thread, does anyone know of anything similar to pull rates as well? Effectively i'm looking to view word count distribution per task with associated rates. 

Many thanks in advance!

I have a follow-up question. I'm using this code for word counts on my projects and it's great! It works at the task level. But I'm not needing it to apply to the job level. Is this possible?

Thanks!!

Hey!!

I was about to create a ticket for this, exactly. The solution is magistral, precisely what I needed.

Thanks so much Lazlo, and Mark & Sancho for asking it :)

Hey colleagues!

Now that XTRF is close to deprecating support for Apache Velocity and asking us to replace it with substitutes written in the Groovy scripting language, does someone have the Groovy version of the wonderful code in this thread for total and weighted words for tasks?

Or would Apache Velocity custom columns not be affected by this deprecation?

Hello Jose, 

Custom columns won't be affected by this change. 

Hey Kinga, wonderful news! thanks a lot!!