UpdateIf With CDS LookUp Columns

(0) Share

Report

Posted on by sammybridge

253

Hello,

I am using CDS here. I am trying to do an UpdateIf and one of my conditions is to ask if the ID number is equal to a variable I am setting. I can't see to get this condition to work for some reason. This is my code. The bit in red is the bit that isn't working. The ID part at the start is the column name in the Entity 'NPI Tasks', however this references another Entity which has IDNew as its Primary Key. I have used this condition on loads of other places in my app and it works fine, however it isn't working in this circumstance.

UpdateIf(

'NPI Tasks',
Stage = Value(taskstage + 1) And Value(Label9.Text) = 0 And ID.IDNew = idvar,
{cr80a_duedate: Today() + 5}
)

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

v-xida-msft on at

Like (0)

Report
Hi @sammybridge ,

Could you please share a bit more about your scenario?

Is the "ID" column a LookUp type column in your 'NPI Tasks' Entity? Could you please show more details about the idvar variable that you mentioned?

Further, do you store the IDNew (Primary Key) value of an record in your 'Another Entity' into the idvar variable?

On your side, please make sure you have stored the IDNew (Primary Key) value (GUID value) of an record in your 'Another Entity' into the idvar variable. If the IDNew (Primary Key) value stored in this idvar variable is a string value, please consider modify your formula as below:

UpdateIf( 'NPI Tasks', Stage = Value(taskstage + 1) && Value(Label9.Text) = 0 && ID.IDNew = GUID(idvar), { cr80a_duedate: Today() + 5 } )

Does the Label9 connect to a field in your 'NPI Tasks' Entity, if true, please replace the Label9.Text formula with actual field name from your 'NPI Tasks' Entity within above formula:

UpdateIf( 'NPI Tasks', Stage = Value(taskstage + 1) && Value('Actual Field Name') = 0 && ID.IDNew = GUID(idvar), { cr80a_duedate: Today() + 5 } )

In addition, you could consider store the specific record from your 'Another Entity' into this idvar variable using the following formula:

Set(idvar, LookUp('Another Entity', FilteredColumn = "Specific Value"))

then modify your UpdateIf formula as below:

UpdateIf( 'NPI Tasks', Stage = Value(taskstage + 1) && Value('Actual Field Name') = 0 && ID.IDNew = idvar.IDNew, // or type idvar.cr80a_IDNew { cr80a_duedate: Today() + 5 } )

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
sammybridge 253 on at

Like (0)

Report

Hi @v-xida-msft ,

I am initially setting the variable by having a button in Gallery with the OnSelect:

Set(
idvar,
ThisItem.IDNew
)

The items of this Gallery is the 'Product Briefs'.

I them then trying to set Due Dates in my other Entity ('NPI Tasks'). This Entity has a LookUp field named ID which is a LookUp of 'Product Briefs'.

I have managed to get the UpdateIf to submit data by changing the code to expand out the Value's, giving me:

UpdateIf(
[@'NPI Tasks'], ID.IDNew = idvar && Stage = Value(taskstage + 1) &&
CountIf([@'NPI Tasks'], ID.IDNew = idvar && 'Task Status' = TaskActivityStatus.Open && Stage = Value(taskstage)) = 0,

{cr80a_duedate: Today() + 3}
)

However this is completely overlooking my initial Filter to only Update the Items in the 'NPI Tasks' Entity where the ID is the same as the one that was previous Set when looking at the 'Product Briefs' Gallery. I only want to Update the items where the ID is the same.

I have tried putting GUID(idvar) but that doesn't work.

My IDNew field is an Autonumber field in 'Product Briefs'.
My ID field in 'NPI Tasks' is a LookUp field to the 'Product Briefs' Entity.
I have a 1:M Relationship set up from 'Product Briefs' to 'NPI Tasks'

I've attached an image to try and explain it.

Thanks

UpdateIf.PNG

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @sammybridge ,

I found that you have added "Stage = Value(taskstage + 1)" condition and "Stage = Value(taskstage)" condition in your UpdateIf function, is there any difference between them?

As an alternative solution, please consider modify your formula as below:

If( CountIf([@'NPI Tasks'], ID.IDNew = idvar && 'Task Status' = TaskActivityStatus.Open && Stage = Value(taskstage)) = 0, UpdateIf( [@'NPI Tasks'], ID.IDNew = idvar && Stage = Value(taskstage + 1), { cr80a_duedate: Today() + 3 } ) )

Please also check if you have specified proper filter condition within your UpdateIf function.

If you only want to update the items in your 'NPI Tasks' Entity where the ID field is bind to same record in your 'Product Briefs', please modify above formula as below:

If( CountIf([@'NPI Tasks'], ID.IDNew = idvar && 'Task Status' = TaskActivityStatus.Open && Stage = Value(taskstage)) = 0, UpdateIf( [@'NPI Tasks'], ID.IDNew = idvar, // Modify filter condition here { cr80a_duedate: Today() + 3 } ) )

Please take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
sammybridge 253 on at

Like (0)

Report

Hi @v-xida-msft ,

So what I am doing is every time a Task is complete, my CountIf is checking to see if all the other Tasks at that Stage are complete. I set a variable when completing the Task to get the Stage of that Task and name this 'taskstage'.

Once I establish that all the Tasks are complete (or as per the code none of them have the Task Status = Open) then I want to UpdateIf my next Stage of Tasks which is why I say where the Stage = (taskstage + 1).

The bit I find most confusing is that the CountIf works perfectly but the UpdateIf doesn't work, despite having the exact same filter in it.

Your first concept below should be exactly what I need to work but yet it does not work. As soon as I take out the filter "ID.IDNew = idvar" it works but it obviously updates for every ID and not just the one I want.

If(
CountIf([@'NPI Tasks'], ID.IDNew = idvar && 'Task Status' = TaskActivityStatus.Open && Stage = Value(taskstage)) = 0,
UpdateIf(
[@'NPI Tasks'],
ID.IDNew = idvar && Stage = Value(taskstage + 1),
{
cr80a_duedate: Today() + 3
}
)
)

Is there anything within CDS lookup fields (my ID.IDNew field is a LookUp) that could be stopping this from working?

The other thing worth noting is that within my Parent Entity of Product Brief's, the IDNew field is an Autonumber field. Would this possibly have an impact?

Thanks

Sammy

Was this reply helpful? Yes No
Verified answer

v-xida-msft on at

Like (0)

Report
HI @sammybridge ,

Do you mean that the UpdateIf function would update all records in your 'NPI Tasks' Entity rather than these records where ID.IDNew = idvar?

Based on the formula that you mentioned, I could not find any syntax error with it. As another solution, please consider modify your formula as below:

If( CountIf([@'NPI Tasks'], ID.IDNew = idvar && 'Task Status' = TaskActivityStatus.Open && Stage = Value(taskstage)) = 0, ForAll( RenameColumns(Filter([@'NPI Tasks'], ID.IDNew = idvar, Stage = Value(taskstage + 1)), "Unique Identifier Column", "Unique Identifier Column1"), Patch( [@'NPI Tasks'], LookUp([@'NPI Tasks'], 'Unique Identifier Column' = 'Unique Identifier Column1'), { cr80a_duedate: Today() + 3 } ) ) )

Note: The 'Unique Identifier Column' represents the Primary Key column (GUID) in your 'NPI Tasks' Entity. On your side, the 'Unique Identifier Column' may be in following format in your Entity:

prefix_npitasksid

Best regards,

Was this reply helpful? Yes No
sammybridge 253 on at

Like (0)

Report

Hi @v-xida-msft ,

The Rename columns method worked thank you very much. I can't understand why the UpdateIf function just doesn't work but the other method works so I am happy to accept that as a solution.

Thanks

Was this reply helpful? Yes No