cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Delegation on patch statement need to revise

 

I'm trying to use MAX and FILTER in the patch statement below.

Can this be revised to get past the large dataset error ?

 

Patch(
'[dbo].[t_nsc_trackcode_trans_time_entry]',
Defaults('[dbo].[t_nsc_trackcode_trans_time_entry]'),
{
NSC_ID_Ref: Max(Filter('[dbo].[t_nsc_trackcode_assigned_DataEntry]',ID_Racfid = lbl_Racfid.Text), Value(NSC_Id)),
opened_at: varDate,
inprogress_or_closed_at: varDate
}
)

 

Thanks

Dave

9 REPLIES 9
Super User III
Super User III

@DAVIDPOWELL 

Please consider changing your Formula to the following:

With({_items:  
    Filter('[dbo].[t_nsc_trackcode_assigned_DataEntry]', ID_Racfid = lbl_Racfid.Text)
    },
    With({_max: Max(_items, Value(NSC_Id))},

        Patch('[dbo].[t_nsc_trackcode_trans_time_entry]',
            Defaults('[dbo].[t_nsc_trackcode_trans_time_entry]'),
            {
                NSC_ID_Ref: _max,
                opened_at: varDate,
                inprogress_or_closed_at: varDate
            }
        )
    )
)

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Post Prodigy
Post Prodigy

 

Randy, is it possible that the Patch statement i had ,

was grabbing a MAX id for an individual because of a delegation issue ? or perhaps a internet connection issue.

The patch just continues when it happens to find an MAX id ?

 

That never occurs on my machine but happens to folks in remote locations...

 

The code you suggested worked fine...Thanks !!

 

Dave

 

Super User III
Super User III

@DAVIDPOWELL 

Glad that worked!

 

Probably not so much of connection issue as much as a timing issue.  Since you were doing a query (filter) inside of the Patch, it is possible that the patch did not get the information needed to perform the patch in time to do the patch.  Also I find conversions (Value) to cause issues with the timing of formulas.

In your original case, those things in the actual max function was most likely tripping you up with delegation issues.  Separating that out resolves it.  For me, I always like to have the most simple and delegable filters, patches, updates, and other functions.  The key to that is pre-evaluating with the With cause. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
Post Prodigy
Post Prodigy

Randy, 

 

Is there a way to break out the Filter  and hold the Max id  as a variable . after the filter   then execute..

and not be contained inside with the patch statement.

 

have this run:  first

With({_items: Filter('[dbo].[t_nsc_trackcode_assigned_DataEntry]', ID_Racfid = lbl_Racfid.Text) },
With({_max: Max(_items, Value(NSC_Id))}, )) 

Super User III
Super User III

@DAVIDPOWELL 

Well, those do run first!  Are you saying you don't want to have it run again?

If that is the case, then you need to utilize a snapshot variable to hold the value.  Ex,

If(Coalesce(glbMaxItem, 0) = 0,
    Set(glbMaxItem,
        With({_items:  
            Filter('[dbo].[t_nsc_trackcode_assigned_DataEntry]', ID_Racfid = lbl_Racfid.Text)
            },
            Max(_items, Value(NSC_Id))
        )
    )
);

Patch('[dbo].[t_nsc_trackcode_trans_time_entry]',
   Defaults('[dbo].[t_nsc_trackcode_trans_time_entry]'),
   {
        NSC_ID_Ref: glbMaxItem,
        opened_at: varDate,
        inprogress_or_closed_at: varDate
   }
)
 

In this case the assumption is that a value of 0 in the glbMaxItem means that it is not set...so grab it with the formula and hold it in the variable...then use it.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Randy, Your code is working fine and thanks again..

On some remote users, The max id is getting grabbed properly.

On my test machine it works fine.

However, it is not working on users who have more than 2000 records.

There are approx.. 5,000 entries per filter.  i confirmed that  2000 record is the max id it is grabbing . ???

in sql server table.

 

 

Thanks

dave

Super User III
Super User III

@DAVIDPOWELL 

Yes, 2000 is the maximum amount of records that you can retrieve in PowerApps.

 

Do you have anything you can sort on?  Like a record date or something.  If so, then you can descend sort your records and then, you'll still only get a maximum of 2000, but your max function will at least be able to work with the more recent records.

 

Otherwise, I would consider using a SQL view for this, or perform an actual SQL query in PowerAutomate to get the max and return it to your app.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Is it possible to add a sort to the with statement you wrote?

i tried using sort on this statement but not working.

flow4.png

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (71,015)