cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sammybridge
Helper IV
Helper IV

UpdateIf With CDS LookUp Columns

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

13.JPG

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-xida-msft
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

13.JPG

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (50,843)