Computed Fields tutorials

[michelle.stone]

As promised in today's PLM Talk - some handy-dandy links for you guys!

Computed Fields: Small but Powerful (see reply below for content from that tutorial)

Seeing Red

W3 Schools SQL Tutorial

Enjoy!

Michelle

[sara.murray]

Hi Michelle,

I was hoping to find more information about computed fields in the referenced tutorial "Small but Powerful", but when I click on it, the link merely goes to the main http://help.autodesk.com/view/PLM/ENU/ page. Is the tutorial still available?

Specifically, my question is how to deal with null data in a computed field formula. In the example below if FACILITY3 is null, the computed field is blank. If FACILITY3 is 0, the computed field shows the correct value (an average rating).

((FACILITY3 * 3) + (FACILITY4 * 4) + (FACILITY5 * 5)) / FACILITYTOT

Alternatively, if the FACILITY3 field could be hidden if the value is 0 but shown if the value is greater than 0, that would work, too.

Thanks,

Sara

[michelle.stone]

Sara - I'll check with our content team about reviving the tutorial (I had it saved locally, so I'll post in a seperate response).

To address your question - a few things to think about.  You could use case statements to solve, or I'd probably just make any fields that are part of the computed field mandatory.

Quick example of a case statement that would work:

CASE WHEN (FACILITY3 is null)
THEN
((FACILITY4 * 4) + (FACILITY5 * 5)) / 2
ELSE
((FACILITY3 * 3) + (FACILITY4 * 4) + (FACILITY5 * 5)) / 3
END

{you'd have to extend for cases where the other facilities are also null - I'd go back to the original suggestion to have them mandatory if you are using them in calculations}

Hope that helps!

Michelle

[michelle.stone]

Below of some examples of how you can use computed fields.  In this first set of examples, you can see how using computed fields gives you immense flexibility for formatting information.

 

Advanced text:

 

Text with a link:

Next up – math!  I’m sure you’ll quickly see how this can be very useful, especially if your current processes rely on Excel. 

 

This can be extended to use more complex calculations.

We can use computed fields for conditioning logic as well.

 

This next example shows a slick way to embed a Google map from the provided address information.

 

Try some fun formatting on your own and take advantage for the flexibility you have in Autodesk PLM 360.  

[sara.murray]

Thank you, Michelle. I went ahead and made them mandatory and filled in a zero or positive integers for each field. I am using the original formula:

((FACILITY3 * 3) + (FACILITY4 * 4) + (FACILITY5 * 5)) / FACILITYTOT
This works for the Facility Average Rating.

However, the fields below that in the matrix with different names are not calculating correctly, even though the field names have been changed in the formula of the computed field section.

For example, if AGENDA3 = 0, AGENDA4 = 1, AGENDA5 = 4, and AGENDATOT = 5 (1 respondent gave a rating of '4', and 4 respondents gave a rating of '5'), AGENDAAVG should be 4.80 but instead it calculates as 4.00. (Screenshots below.)

I tried deleting the original Agenda-Avg field and re-creating from scratch a new Agenda-Average field, but still have the same issue.

Any guidance for this problem?

[michelle.stone]

Sara - make sure you are referencing the correct Field ID in the computed field.  4 is the correct average rating for the first row of your matrix.  The field IDs for the other inputs differ so you'll need to update the calculated field formula for each subsequent row.

Michelle

[sara.murray]

Yes, I had already updated the computed field formula for each subsequent row, as in my second screenshot above for the "Agenda" row. Therefore it is hard to figure out why the average rating for each subsequent row is not showing properly.

I will include another screenshot here that was too big for my previous posting:

Thanks in advance for any additional help!

[michelle.stone]

Sara - I'll create a support case and have someone from the Technical Support team work with you more closely with you.

Thanks,

Michelle

[sara.murray]

I appreciate your response; do you have a support case number that I can reference?

Thanks,

Sara

[michelle.stone]

Sara - it's case #08146934.

 

[j.hendrickson]

I found the COALESCE function works well for dealing with NULL values. Much slicker than a CASE statement. I come from an Oracle SQL background but as someone mentioned in another post PLM 360 seems to use a PostgreSQL 8.1 dialect and that's where I found this function.

( COALESCE(FACILITY3,0) * 3 + COALESCE(FACILITY4,0) * 4 + COALESCE(FACILITY5,0) * 5 ) / FACILITYTOT

[sara.murray]

Thank you to the community... Bastien found that each field needed to be of data type FLOAT in order for the computed field of type FLOAT to function properly. Also, the COALESCE function is definitely slick and provides the desired look to the matrix!

So the updated computed field formula (return a 1 in the denominator if FACILITYTOTFLOAT is null):

( COALESCE(FACILITY3FLOAT,0) * 3 + COALESCE(FACILITY4FLOAT,0) * 4 + COALESCE(FACILITY5FLOAT,0) * 5 ) / COALESCE(FACILITYTOTFLOAT,1)

Screenshots of the field definitions and matrix display that are working now are in the attachments.

[pnigam]

Hi Michelle,

I am not sure what I am doing wrong here but I cannot see the HTML TEXT  when I added my Computed Field as Special instructions here.

Thanks

Parul

[gasevsm]

Greetings,

Instead of <span>, try one of these or a combination of style's font-family, font-size, font-color attributes:

<h1 style="font-family:verdana">This is a heading</h1>
<p style="font-family:courier">This is a paragraph.</p>

<h1 style="font-size:300%">This is a heading</h1>
<p style="font-size:160%">This is a paragraph.</p>

<h1 style="color:blue">This is a heading</h1>
<p style="color:red">This is a paragraph.</p>

HTH,

[pnigam]

Hi ,

I tried what you suggested but still no luck  not sure why !

Thanks

Parul

[pnigam]

Never mind ! I had to do multiple Refresh and now it seems to be working fine

[michelle.stone]

Glad it worked!

[wendy.salas]

Hello! Is this iframe approach supported in Modern UI? Thanks!

[giliar.defriesperez]

---

@wendy.salas wrote:

Hello! Is this iframe approach supported in Modern UI? Thanks!

---

Hi Wendy,

No, it won't. We're adding support for rendering HTML in Computed Fields to Modern UI soon, but <script> and <iframe> tags are not going to be part of that iteration.