cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
Community Support
Community Support

Re: UpdateIf With CDS LookUp Columns

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
Highlighted
Community Support
Community Support

Re: UpdateIf With CDS LookUp Columns

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.
Highlighted
Helper III
Helper III

Re: UpdateIf With CDS LookUp Columns

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

Highlighted
Community Support
Community Support

Re: UpdateIf With CDS LookUp Columns

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.
Highlighted
Helper III
Helper III

Re: UpdateIf With CDS LookUp Columns

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

Highlighted
Community Support
Community Support

Re: UpdateIf With CDS LookUp Columns

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

Highlighted
Helper III
Helper III

Re: UpdateIf With CDS LookUp Columns

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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,399)