Showing results for 
Search instead for 
Did you mean: 
Helper V
Helper V

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


Thank you for your help @RezaDorrani ,

I hope one day I can reach your experience

look it works fine:


Captura de Pantalla 2019-08-24 a la(s) 12.40.44.png


I have one last question about this

Can the delegations that mark this formula bring me problems?


Or could such delegations be avoided?

Hi @octaking 


This is where things get complicated


So I would do something like this


OnAppStart (get user email id and also get the user object from Users entity)

There is no delegation warning here 



Set(varuserObj,LookUp(Users,('User Name')=(varUserEmail)));


Then OnVisible of screen (screen where approval data is to be shown)


// This will get all active approvals from Approvals list - no delegation 

// But since we are using a collection it will only store 2000 active approvals (If you believe that at a time your

// system will exceed 2000 active approvals - then you would have to check the collection count and if 2000 query

// the next set and keep loading into the same collection

// Note a collection can hold N number of records


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


// We will then query the Approval Requests and get all requests in the system pertaining to logged in user account

// and load it into another collection

// once again no delegation but collection will only store 2k records

// These are sorted based on created date so will get latest 2k records 


ClearCollect(colMyApprovalRequests, SortByColumns( Filter( 'Approval Requests', ('Owning User Index') = Upper(Text(varuserObj.User))), "createdon", Descending))



Then on Gallery

// no delegation error 🙂

SortByColumns( Filter( colMyApprovalRequests, 'Approval Id Index' in colActiveApprovals.Approval ), "createdon", Descending)



Reza Dorrani


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


Hi @RezaDorrani 


I tried it Reza

and it is true that it does not give any delegation but has not returned records. Do you think I did something wrong?


Captura de Pantalla 2019-08-24 a la(s) 18.43.19.png


Captura de Pantalla 2019-08-24 a la(s) 18.44.06.png


Captura de Pantalla 2019-08-24 a la(s) 18.44.19.png

Hi @octaking 


Firstly check to see if varUserObj variable has any value in it

You can check that by going to View -> Variables -> and see its value (it would be a record) click on it to see its contents - it should have the logged in users User entity object data


Next in my case in the below formula :-

ClearCollect(colMyApprovalRequests, SortByColumns( Filter( 'Approval Requests', ('Owning User Index') = Upper(Text(varuserObj.User))), "createdon", Descending))


I use upper because I noticed in the Approval Requests entity the Owning user index field has all data in caps

I am asusming its same case for you so do check


Also, check counts of all collections to see if data is there


Hi, @RezaDorrani 

the variable has no records

Why does this happen?


Captura de Pantalla 2019-08-24 a la(s) 21.00.57.png

yes, in my case also 'Owning User Index' is in uppercase

Its probably because the username field in User entity and the email variable are not matching

Check in cds database to see value in user name field

Also try lower function for both and compare

I will be online back tomorrow

Hello @RezaDorrani 
I checked it and both are the same


Captura de Pantalla 2019-08-25 a la(s) 07.51.54.png



Hi @octaking 


Was working in my case so we will have to go step by step in your case


varUserObject is empty for you?

Hi @RezaDorrani 

thanks for answering I appreciate your time,

exactly my value of varUserObject has no record


You can see in the following images the steps that I have followed:


In OnStart

First I declare the variable Email which its value is User ().Email


Then I declare the varUserObject variable whose value is obtained from (users )data source where the NameUser = to the Email variable that I previously declared:



download (10).png


then on OnVisible of the Screen where I want to show the records are the following 2 collections:

where the colMyApprovalRequests collection filters by:

'Approval Requests', ('Owning User Index') = Upper(Text(varuserObj.Usuario)download (11).png


Then in my gallery I have the formula that bears both collections:

SortByColumns(Filter( colMyApprovalRequests, 'Approval Id Index' in colActiveApprovals.Approval ), "createdon", Descending)


download (13).png


and finally my collection of (colMyApprovalRequests):

download (14).png


CDS I verified that NameUser is equal to email


I would have liked to have the same result as your Reza



Helpful resources

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!


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 (41,173)