cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
octaking
Level: Powered On

formula does not return records

Hello, I have the following formula that does not give any error but does not bring me the records that I must return

I'm sure that's how the owner gets it and I know it because it works in another gallery

and the filter Approval.Status = 'Status (Approvals)'. Activate The status is fine because in the same way it is already in a gallery and works correctly

but when I want to do this in a single formula, it doesn't return the records:

 

SortByColumns( Filter( AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal'), Approval.Status = 'Status (Approvals)'.Active, Owner_Email = User().Email), "createdon", Descending)

 

download (9).png

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: formula does not return records

Hi @octaking 

 

I got this working

 

So if you actually look at the Approval Request entity - the lookup to Approval is empty

There is no data in there

 

hence 

SortByColumns( Filter( AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal'), Approval.Status = 'Status (Approvals)'.Active, Lower(Owner_Email) = Lower(User().Email)), "createdon", Descending)

will give no data

 

whereas

SortByColumns( Filter( AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal'), Lower(Owner_Email) = Lower(User().Email)), "createdon", Descending

will return results

 

 

So now what has to be done is this,

first get a collection of all active (running) approvals from the Approval entity

 

ClearCollect(colActiveApprovals,Filter(Approvals, Status = 'Status (Approvals)'.Active))

You can do this on page visisble or button click

 

then for your gallery run the following :

 

SortByColumns( Filter( 'Approval Requests', 'Approval Id Index' in colActiveApprovals.Approval && AsType(Owner,[@Users] ).'User Name' = User().Email ), "createdon", Descending)

 

This will give you the results you are looking for

 

I did notice all items in Approval Request have status Active - so im guessing that is just a default value set which never changes and does not reflect the true status of the approval

 

Regards,

Reza Dorrani

 

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

25 REPLIES 25
Dual Super User
Dual Super User

Re: formula does not return records

Hi @octaking 

 

 

as a test 

 

add AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal') to a collection

 

ClearCollect(coltemp, AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal'))

 

and check and see the value format in Owner_Email

I have a feeling it does not match the User().Email format

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

octaking
Level: Powered On

Re: formula does not return records

Hello @RezaDorrani ,

thanks for answering

I did the test you indicated and the format of Owner_Email is as follows:

 

 

 

Captura de Pantalla 2019-08-23 a la(s) 21.24.46.png

Dual Super User
Dual Super User

Re: formula does not return records

Hi @octaking 

 

try

 

SortByColumns( Filter( AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal'), Approval.Status = 'Status (Approvals)'.Active, Lower(Owner_Email) = Lower(User().Email)), "createdon", Descending)

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

octaking
Level: Powered On

Re: formula does not return records

Hi @RezaDorrani  

still without returning records

Captura de Pantalla 2019-08-23 a la(s) 21.46.51.png

 

Look, this is my other Approval gallery and it works well, it brings me the pending requests from the owner.

Captura de Pantalla 2019-08-23 a la(s) 21.51.17.png

 

 

Dual Super User
Dual Super User

Re: formula does not return records

Hi @octaking 

 

only doffernece I see is the Status part of the formula

try changing that also and check

Highlighted
octaking
Level: Powered On

Re: formula does not return records

Hi @RezaDorrani 

Look at this filter in a view of the entity if it returns the slopes:

 

Owner = CurrentUser and Approva.Status = Active

 

Captura de Pantalla 2019-08-23 a la(s) 22.08.47.png

 

The result = Pending approvals per current user:

Captura de Pantalla 2019-08-23 a la(s) 22.08.27.png

 

It is strange that the formula does not work

since this has the same logic

Dual Super User
Dual Super User

Re: formula does not return records

@octaking 

 

Yes that is weird 

 

octaking
Level: Powered On

Re: formula does not return records

Yes, I changed the status of Active to zero but it still gives the same result

Dual Super User
Dual Super User

Re: formula does not return records

Hi @octaking 

 

I got this working

 

So if you actually look at the Approval Request entity - the lookup to Approval is empty

There is no data in there

 

hence 

SortByColumns( Filter( AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal'), Approval.Status = 'Status (Approvals)'.Active, Lower(Owner_Email) = Lower(User().Email)), "createdon", Descending)

will give no data

 

whereas

SortByColumns( Filter( AddColumns('Approval Requests', "Owner_Email", AsType(Owner, Usuarios).'Correo electrónico principal'), Lower(Owner_Email) = Lower(User().Email)), "createdon", Descending

will return results

 

 

So now what has to be done is this,

first get a collection of all active (running) approvals from the Approval entity

 

ClearCollect(colActiveApprovals,Filter(Approvals, Status = 'Status (Approvals)'.Active))

You can do this on page visisble or button click

 

then for your gallery run the following :

 

SortByColumns( Filter( 'Approval Requests', 'Approval Id Index' in colActiveApprovals.Approval && AsType(Owner,[@Users] ).'User Name' = User().Email ), "createdon", Descending)

 

This will give you the results you are looking for

 

I did notice all items in Approval Request have status Active - so im guessing that is just a default value set which never changes and does not reflect the true status of the approval

 

Regards,

Reza Dorrani

 

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,064)