SOLVED

Do Until - Get File Properties ... Invalid Template ...

%3CLINGO-SUB%20id%3D%22lingo-sub-175602%22%20slang%3D%22en-US%22%3EDo%20Until%20-%20Get%20File%20Properties%20...%20Invalid%20Template%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-175602%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20I'm%20building%20an%20approval%20flow%20that%20is%20triggered%20when%20a%20file%20is%20added%20to%20a%20library.%3C%2FP%3E%0A%3CP%3EThere%20are%20a%20number%20of%20columns%20in%20the%20library%20which%20are%20required%20including%20the%20approver.%20Obviously%2C%20these%20are%20not%20populated%20when%20the%20flow%20triggers.%20Therefore%20I%20have%20added%20a%20'do%20until'%20that%20gets%20the%20file%20properties%20until%20the%20approver%20has%20been%20selected.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20had%20this%20working%20okay%20when%20testing%20with%20a%20single%20field%20(approver)%20with%20the%20test%20condition%20(not%20empty)%20directly%20in%20the%20do%20until.%20But%20it%20suddenly%20stopped%20working%20in%20the%20last%203-5%20days.%20Therefore%2C%20I%20started%20testing%20with%20some%20of%20the%20other%20required%20fields.%20Including%20using%20a%20variable%20in%20the%20Do%20Until%20condition%20and%20changing%20this%20within%20the%20Do%20Until%20using%20a%20condition.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20virtually%20all%20cases%20I%20get%20this%20%3CSTRONG%3E'InvalidTemplate'%3C%2FSTRONG%3E%20error%20either%20directly%20in%20the%20%3CSTRONG%3EDo%20Until%3C%2FSTRONG%3E%20condition%20or%20the%20separate%20condition%20within%20the%20%3CSTRONG%3EDo%20Until%3C%2FSTRONG%3E%20that%20tests%20the%20empty%2Fnull%2Fblank%20fields%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30878i1FEDD105CBEA1056%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20this%20is%20correct%2C%20then%20why%20do%20the%20columns%20appear%20as%20available%20when%20building%20the%20flow%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInterestingly%2C%20in%20my%20most%20recent%20flow%2C%20the%20remaining%20steps%20in%20the%20flow%20execute%20correctly%20despite%20this%20error.%20As%20you%20can%20see%20here%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30879i82987D960051995D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20reference%2C%20here%20is%20my%20flow%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30880i66E2F271BFC493D8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%222018-03-26%2000_25_31-Microsoft%20Edge.png%22%20title%3D%222018-03-26%2000_25_31-Microsoft%20Edge.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F30881i413E93430A974F96%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222018-03-26%2000_33_20-Microsoft%20Edge.png%22%20title%3D%222018-03-26%2000_33_20-Microsoft%20Edge.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20ID%20used%20in%20%3CSTRONG%3E'Get%20file%20properties'%3C%2FSTRONG%3E%20is%20from%20the%20%3CSTRONG%3E%3CEM%3E'When%20a%20file%20is%20created'%3C%2FEM%3E%3C%2FSTRONG%3E%20step%20(triggerBody()%3F%5B'ID'%5D).%3C%2FP%3E%0A%3CP%3EThe%20InvoiceAmount%20used%20in%20the%20%3CSTRONG%3E'Condition'%3C%2FSTRONG%3E%20step%20is%20from%20the%20%3CSTRONG%3E'Get%20file%20properties'%3C%2FSTRONG%3E%20step.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20now%20going%20to%20create%20a%20new%20library%20and%20try%20again.%20Because%20while%20it%20seems%20to%20be%20working%2C%20I'm%20not%20confident%20that%20there%20is%20some%20underlying%20issue.%20Or%20perhaps%20I'm%20betraying%20my%20lack%20of%20knowledge!%20Happy%20to%20be%20educated.%20%3A)%3C%2Fimg%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-175602%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-176441%22%20slang%3D%22en-US%22%3ERe%3A%20Do%20Until%20-%20Get%20File%20Properties%20...%20Invalid%20Template%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-176441%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20i've%20solved%20it%20and%20discovered%20that%20this%20is%20what%20coalesce%20is%20for.%20Here%20is%20my%20working%20Do%20Until%20condition%20without%20needing%20to%20assign%20default%20values%20to%20the%20columns%20in%20the%20library%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%40and(not(empty(body('Get_file_properties')%3F%5B'Approver'%5D))%2Cnot(equals(coalesce(body('Get_file_properties')%3F%5B'InvoiceAmountExclGST'%5D%2C%20'0')%2C%20'0'))%2Cnot(equals(coalesce(body('Get_file_properties')%3F%5B'InvoiceDate'%5D%2C%20'19000101')%2C%20'19000101'))%2Cnot(equals(coalesce(body('Get_file_properties')%3F%5B'InvoiceFrom'%5D%2C%20'Nobody')%2C%20'Nobody'))%2Cnot(equals(coalesce(body('Get_file_properties')%3F%5B'InvoiceNumber'%5D%2C%20'0')%2C%20'0')))%3C%2FPRE%3E%0A%3CP%3EIf%20i%20understand%20coalesce%20correctly%2C%20the%20fallback%20value%20could%20be%20any%20string%20value.%20I%20just%20chose%20values%20that%20kind%20of%20made%20sense%20for%20the%20type%20of%20each%20column.%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3EThe%20Do%20Until%20%26gt%3B%20Get%20file%20properties%20loop%20now%20keeps%20looping%20until%20all%20the%20required%20fields%20are%20populated.%20Without%20generating%20the%20InvalidTemplate%20error.%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3EI'll%20mark%20this%20as%20solved%20and%20hope%20someone%20finds%20this%20useful.%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3EIan.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-176082%22%20slang%3D%22en-US%22%3ERe%3A%20Do%20Until%20-%20Get%20File%20Properties%20...%20Invalid%20Template%20...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-176082%22%20slang%3D%22en-US%22%3E%3CP%3EWhile%20I%20don't%20really%20understand%20why%2C%20I%20think%20that%20I've%20at%20least%20got%20a%20workaround%20for%20my%20template%20error%20problem.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20seems%20that%20'Get%20file%20properties'%20action%20and%20'When%20a%20file%20is%20created%20(properties%20only)'%20trigger%20do%20not%20output%20columns%20that%20have%20no%20data%20in%20them.%20Except%20if%20the%20column%20is%20a%20person%2Fgroup%20column.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20when%20initially%20constructing%20my%20flow%2C%20my%20condition%20step%20only%20tested%20whether%20the%20'Approver'%20column%20was%20empty.%20My%20flow%20worked%20because%20the%20'Approver'%20column%20is%20output%20by%20the%20'When%20a%20file%20is%20created'%20trigger%20and%20the%20'Get%20file%20properties'%20action%20even%20though%20it%20is%20blank%2Fempty.%20However%2C%20when%20I%20put%20my%20'InvoiceAmount'%2C%20'InvoiceFrom'%20or%20'InvoiceDate'%20columns%20in%20the%20condition%2C%20I%20get%20the%20TemplateError%20because%20the%20columns%20are%20not%20available%20in%20the%20output%20of%20'Get%20file%20properties'%20action.%20But%20when%20I%20put%20a%20default%20value%20in%20the%20'InvoiceAmount'%20etc.%20columns%2C%20they%20appear%20in%20the%20'Get%20file%20properties'%20action%20output%20and%20I%20don't%20see%20the%20TemplateError.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20not%20desired%20behaviour%20as%20I%20would%20like%20the%20person%20adding%20an%20invoice%20to%20be%20prompted%20for%20the%20required%20fields%20before%20the%20invoice%20is%20sent%20for%20approval.%20However%2C%20I%20can%20workaround%20by%20assigning%20default%20non-sensible%20values%20to%20the%20columns.%20Then%20test%20these%20in%20my%20condition%20and%20only%20send%20the%20invoice%20for%20approval%20once%20the%20columns%20have%20different%20values.%20Which%20are%20hopefully%20more%20sensible%20values.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20probably%20still%20bit%20of%20a%20noob%20at%20this%20stuff%20%3A)%3C%2Fimg%3E%20but%20I'm%20learning.%20Is%20there%20a%20way%20to%20test%20if%20the%20column%20exists%20in%20the%20output%20before%20testing%20the%20value%3F%20Is%20this%20what%20the%20'coalesce'%20function%20is%20for%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIan.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hello. I'm building an approval flow that is triggered when a file is added to a library.

There are a number of columns in the library which are required including the approver. Obviously, these are not populated when the flow triggers. Therefore I have added a 'do until' that gets the file properties until the approver has been selected.

 

I had this working okay when testing with a single field (approver) with the test condition (not empty) directly in the do until. But it suddenly stopped working in the last 3-5 days. Therefore, I started testing with some of the other required fields. Including using a variable in the Do Until condition and changing this within the Do Until using a condition.

 

In virtually all cases I get this 'InvalidTemplate' error either directly in the Do Until condition or the separate condition within the Do Until that tests the empty/null/blank fields:

 

image.png

 

If this is correct, then why do the columns appear as available when building the flow?

 

Interestingly, in my most recent flow, the remaining steps in the flow execute correctly despite this error. As you can see here:

 

image.png

 

For reference, here is my flow:

 

2018-03-26 00_25_31-Microsoft Edge.png

2018-03-26 00_33_20-Microsoft Edge.png

The ID used in 'Get file properties' is from the 'When a file is created' step (triggerBody()?['ID']).

The InvoiceAmount used in the 'Condition' step is from the 'Get file properties' step.

 

I'm now going to create a new library and try again. Because while it seems to be working, I'm not confident that there is some underlying issue. Or perhaps I'm betraying my lack of knowledge! Happy to be educated. :)

 

Thanks.

 

Ian.

2 Replies

While I don't really understand why, I think that I've at least got a workaround for my template error problem.

 

It seems that 'Get file properties' action and 'When a file is created (properties only)' trigger do not output columns that have no data in them. Except if the column is a person/group column. 

 

For example, when initially constructing my flow, my condition step only tested whether the 'Approver' column was empty. My flow worked because the 'Approver' column is output by the 'When a file is created' trigger and the 'Get file properties' action even though it is blank/empty. However, when I put my 'InvoiceAmount', 'InvoiceFrom' or 'InvoiceDate' columns in the condition, I get the TemplateError because the columns are not available in the output of 'Get file properties' action. But when I put a default value in the 'InvoiceAmount' etc. columns, they appear in the 'Get file properties' action output and I don't see the TemplateError.

 

This is not desired behaviour as I would like the person adding an invoice to be prompted for the required fields before the invoice is sent for approval. However, I can workaround by assigning default non-sensible values to the columns. Then test these in my condition and only send the invoice for approval once the columns have different values. Which are hopefully more sensible values.

 

I'm probably still bit of a noob at this stuff :) but I'm learning. Is there a way to test if the column exists in the output before testing the value? Is this what the 'coalesce' function is for?

 

Ian.

Highlighted
Best Response confirmed by Ian Macnaughtan (Contributor)
Solution

I think i've solved it and discovered that this is what coalesce is for. Here is my working Do Until condition without needing to assign default values to the columns in the library:

 

@and(not(empty(body('Get_file_properties')?['Approver'])),not(equals(coalesce(body('Get_file_properties')?['InvoiceAmountExclGST'], '0'), '0')),not(equals(coalesce(body('Get_file_properties')?['InvoiceDate'], '19000101'), '19000101')),not(equals(coalesce(body('Get_file_properties')?['InvoiceFrom'], 'Nobody'), 'Nobody')),not(equals(coalesce(body('Get_file_properties')?['InvoiceNumber'], '0'), '0')))

If i understand coalesce correctly, the fallback value could be any string value. I just chose values that kind of made sense for the type of each column.
 
The Do Until > Get file properties loop now keeps looping until all the required fields are populated. Without generating the InvalidTemplate error.
 
I'll mark this as solved and hope someone finds this useful.
 
Ian.