How to use UDF values in FSM HTML report?

[DAcker]

Hello experts, 
I am struggling with the syntax to embed UDF values in FSM HTML Checkout report.

I analyzed the data.js file and the UDF value are displayed in this format:

However, how should the syntax in the template.js look like? I tested different versions (e.g. udf.MILEAGE_NOTES) but they are not working... 

Can someone please help?

Thanks a lot!
Best regards,
Deborah

@krzysztof_szalach Do you have a tip?

[krzysztof_szalach · Accepted solution]

Okay, so it took me a while, but @DAcker I've found some sort of generic solution.

In template.js add for getMileagesTableDescriptor new column:

{
    headerKey: 'Mileage_Udf_Notes_L', udfValue: 'MILEAGE_NOTES',
}

then in template.html in genericTableTemplate for <td> entry:

{{else if udfValue}}
    {{#each data-row.udfValues}}
        {{#compare meta.name "==" column.udfValue}}
            {{value}}
        {{/compare}}
    {{/each}}

 Nów, how it works:

- when generating generic table it will check if attribute "udfValue" in column is set. If yes, it iterates through all udfValues for this row and checks if meta.name matches the attribute value. This way you can use it in any table. 

Full code:
genericTableTemplate

<script id="genericTableTemplate" type="text/x-handlebars-template">
    {{#if dataGroups}}
        {{#gt dataGroups.length 0}}
            <section class="generic-table">
                <h2>{{ localize titleKey }}</h2>
                <table>
                    <thead>
                    <tr>
                        {{#each columns}}
                            <th>{{ localize headerKey }}</th>
                        {{/each}}
                    </tr>
                    </thead>

                    <tbody>
                    {{#each dataGroups as |data-group|}}
                        {{#each . as |data-row|}}
                            <tr>
                                {{#each ../../columns as |column|}}
                                    <td>
                                        {{#if propertyName}}
                                            {{formatValue (get propertyName data-row) column }}

                                        {{else if aggregate}}
                                            {{formatValue (aggregateValues data-row column) column }}

                                        {{else if udfValue}}
                                            {{#each data-row.udfValues}}
                                                {{#compare meta.name "==" column.udfValue}}
                                                    {{value}}
                                                {{/compare}}
                                            {{/each}}

                                        {{/if}}
                                    </td>
                                {{/each}}
                            </tr>
                        {{/each}}
                        <tr>
                            {{#each ../columns as |column|}}
                                <td>
                                    {{#if column.footer}}
                                        <div class="column-footer">
                                            {{#if column.footer.formatFooter}}
                                                {{formatValue (callFunction column.footer.valueProvider ../this/this) column}}
                                            {{else}}
                                                {{callFunction column.footer.valueProvider ../this/this}}
                                            {{/if}}
                                        </div>
                                    {{/if}}
                                </td>
                            {{/each}}
                        </tr>
                    {{/each}}
                    </tbody>
                </table>
            </section>
        {{/gt}}
    {{/if}}
</script>

getMileagesTableDescriptor

const getMileagesTableDescriptor = (mileages, localization) => ({
    titleKey: 'EntityPluralName_Mileage',
    columns: [
        {
            headerKey: 'Mileage_StartDate', propertyName: 'travelStartDateTime', templateId: 'dateTimeTemplate', templateOptions: {formatString: 'L'},
            footer: {
                valueProvider: _ => localization.total,
                formatFooter: false
            }
        },
        {headerKey: 'ExpenseMaterialMileage_Detail_CreatePerson_L', aggregate: ['createPerson.firstName', 'createPerson.lastName'], operator: 'concat'},
        {headerKey: 'Mileage_Detail_From_L', propertyName: 'source'},
        {headerKey: 'Mileage_Detail_To_L', propertyName: 'destination'},
        {
            headerKey: 'Mileage_Detail_Distance_L', propertyName: 'distance', templateId: 'numberTemplate',
            footer: {
                valueProvider: mileages => mileages.map(mileage => mileage.distance).reduce((acc, current) => acc + current, 0),
                formatFooter: true
            }
        },
        {
            headerKey: 'Mileage_Detail_TravelDuration_L', aggregate: ['travelEndDateTime', 'travelStartDateTime'], operator: 'difference', templateId: 'durationTemplate',
            footer: {
                valueProvider: mileages => mileages.filter(mileage => mileage.travelStartDateTime && mileage.travelEndDateTime).map(mileage => mileage.travelEndDateTime - mileage.travelStartDateTime).reduce((acc, current) => acc + current, 0),
                formatFooter: true
            }
        },
        {headerKey: 'Mileage_Udf_Notes_L', udfValue: 'MILEAGE_NOTES'}
        ],
    dataGroups: mileages && mileages.length ? [mileages] : []
});

Although for me column header is not displayed even I've added key to localization file, but I will leave it up to you.

[DAcker · Accepted solution]

Thanks so much!!! Yes, this is working.

[DAcker · Accepted solution]

Thanks so much!!! Yes, this is working. The column header I already figured out myself. If you add the description of the column header in each translation file, it works. So in my case I add an entry for the /translations/de.js and /translations.en.js. ("Mileage_Udf_Notes_L":"Notizen" this makes the column header description)

[DAcker · Accepted solution]

What language is this inline coding? If I want to build up knowledge about... To understand the code rudimentary is not problem. But to code this by myself - no I would never have thought of that myself

[krzysztof_szalach · Accepted solution]

It's Celigo - https://docs.celigo.com/hc/en-us/articles/360039227731-Handlebars-overview here is very nice explanation about the handlebars 🙂

[DAcker · Accepted solution]

I would like to add a certain UDF value into the aggregate expression. Is this possible? In Jasper it was very easy to put multiple value in one data cell.. But it seems that it is not or very difficult to integrate a udf value into the aggregate. Do you have a code example or tipp? (code line in getMileagesTableDescriptor that is of course not working: {headerKey: 'ExpenseMaterialMileage_Detail_CreatePerson_L', aggregate: ['createPerson.firstName', 'createPerson.lastName', udfValue: 'Subdienstleister_Mitarbeiter'], operator: 'concat'},)

[krzysztof_szalach · Accepted solution]

This will be more tricky. What I can think of is that you should modify aggregateValues custom function (in handlebarsSapHelpers.js) in a way that in "prop => Handlebars.helpers.get(prop, context)" it checks first if prop is udfValue (for example if it starts with) and then in such case, it should do something similar to what I did in previous response - so it gets the udfValues and iterates to find that specific one. But this time, this will be written in pure JS, not in Celigo syntax. Or even more generic approach would be to create overridden method of "get", that would check for udfValue. This way it would work across all the report without any additional effort.

[krzysztof_szalach · Accepted solution]

Okay, so this is example way of solving this for aggregateValues function:

[krzysztof_szalach · Accepted solution]

Handlebars.registerHelper('aggregateValues', (context, columnDefinition) => { if (columnDefinition.operator === undefined) { return; } if (columnDefinition.operator == 'concat') { return columnDefinition.aggregate.map(prop => { if (prop.startsWith('udfValue.')) { let udfValue = prop.split('.')[1]; return context.udfValues.find(udf => udf.meta.name === udfValue).value; } else { return Handlebars.helpers.get(prop, context); } }).reduce((acc, current) => acc + ' ' + current, ''); } if (columnDefinition.operator == 'difference') { return columnDefinition.aggregate.map(prop => Handlebars.helpers.get(prop, context)).filter(val => val).reduce((acc, current) => acc - current); } });

[krzysztof_szalach · Accepted solution]

How it works - if the prop value starts with udfValue.* then it search for udfValues with the name of it and returns its value. But keep in mind that it's just a POC - you need to add for example null checks or cover other edge cases (like missing udfValues etc). In template.js you can then use it as simple as "{headerKey: 'Mileage_Udf_Notes_L', aggregate: ['createPerson.firstName', 'udfValue.MILEAGE_NOTES'], operator: 'concat'}". Hope you can now improve it - for example with creating some generic function that takes care of it.

[DAcker · Accepted solution]

Thanks so much! You made my day 🙂 I added the exception handling for udfValue null and tested it, it works. 🙂

[krzysztof_szalach · Accepted solution]

Nice, I'm glad that I was able to help you 🙂

[DAcker · Accepted solution]

Hello Krzysztof, I add the following as expection handling for UDF null values.