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

New Item property caused error: Invalid formula, expected a value compatible with DataSource

I have 3 data sources, but most everything revolves around 'Accounts' from CRM. Initially, my gallery's Items property was this:

SortByColumns(Search(Accounts, TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"), "name", If(SortDescending1, Descending, Ascending))

My DetailForm and EditForm had the Items property of BrowseGallery.Selected and everything worked fine. Then, I changed my gallery's Items property to involve a collection so I could toggle a filter to see all user owned accounts and only current user owned accounts. This is my new gallery Item's property:

SortByColumns(Search(If(UserToggle, Filter(UserCollection, CurrUser = true), UserCollection), TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"), "name", If(SortDescending1, Descending, Ascending))

Now I get the invalid formula, expected a value compatible with DataSource when I try to select a record to view or edit. If I change the DetailForm and EditForm Items properties to the collection name 'UserCollection' then everything shows up, but any changes made on the forms will not reflect in CRM because the DataSource is just the collection. I have played with setting the current record to a variable and PowerApps does not seem to like that either.

 

Edit: I have also tried deleting and reconnecting the data source

3 ACCEPTED SOLUTIONS

Accepted Solutions
KroonOfficeSol
Level 10

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

@MitchC3

 

Easiest way is just to filter your datasource without introducing a collection in your case. Do this

SortByColumns(
	Search(
		Filter(Accounts
			,!UserToggle.Value || ColumnToCheck = CurrUser // Here you should know the identifier in the table/list for the user and have a match in your CurrUser variable
		)
		, TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"
	)
	, "name"
	, If(SortDescending1, Descending, Ascending)
)
!UserToggle.Value // this part gives a true on the UserToggle beïng false, showing all the records
ColumnToCheck = CurrUser // this filters your datasource on the condition that the account belongs to the customer.
// the second line will take effect when the toggle is set to true, because then the first is false. 

It's as easy as that.

 

Hope this helps.

 

Paul

 

 

TimRohr
Level 10

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

I'd have to do some testing, but I think when you embed the LookUp() in a Filter, or possibly when you embed the Lookup() in a Filter while simultaneously referencing properties re: the User, LookUp stops being truly delegable.

 

Try this:

Assign a variable to be the result of the LookUp() at some point prior to needing it in the Gallery (it could be in the OnVisible event of the screen, or the OnStart event of the app). Either UpdateContext({CurrUser:LookUp(....)} if you are doing it on the screen itself, or Set(CurrUser, LookUp(...)) if you are doing it in the OnStart. Then pass that variable into the Filter() statement. I think your delegation warning should go away.

TimRohr
Level 10

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

One more suggestion, then...

 

Go back and look at @KroonOfficeSol's post where he suggested a Filter() with an OR construction.

SortByColumns( 
Search(
Filter(
Accounts,
!UserToggle.Value || Owner = CurrentUser
),
TextSearchBox1.Text, "address1_city","name","address1_stateorprovince" ), "name", If(SortDescending1, Descending, Ascending) )

That would remove the If() completely with a clever trick of handling the UserToggle.

 

If that doesn't work, I might be out of suggestions, but post back. Maybe something you encounter will spawn another thought. Good luck!

16 REPLIES 16
TimRohr
Level 10

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

First, apologies if this aims below your knowledge. Sometimes it helps to start at the beginning, just in case it's somethign you know but happen to be overlooking.

That said...

A Collection is really nothing more than a virtual table. As you use it, it is a virtualized copy of your CRM data. It is NOT, however, a live pointer to your CRM data. It is a snapshot of the data as it existed when you did your Collect()-ing. Therefore, if you set the datasource of your Add/Edit forms to be a Collection, you are modifying the data in your virtual table, and NOT in CRM.

...it's like mundane temporal theory and a split timeline... you (or your data) exist in both places, but what happens to you in one timeline (Collection) doesn't affect the other timeline (CRM).

 

If you're not a sci-fi fan, just pretend like that previous para didn't happen.

 

Now, you can limit the Gallery to the Current User by use of a Collection. You can. In that case, you want to set the datasource of your Add/Edit forms to be the live CRM datasource, but you want to set the Item to be:

First( Filter( yourCRMDatacriteria ) )

...where criteria would limit the returns from your live CRM to the one item you have selected from your Gallery. You can do that by limiting the returns to where the GUID of your record equals yourGallery.Selected.yourGuid, or you can write a variable in your OnSelect from your Gallery before you navigate to the Add/Edit form, and then reference that variable in the criteria.

Be aware that this will create a possible disparity between the CRM live data and the Collection's data. Your form will update the CRM data, but your Collection (another timeline, remember) will still be holding the old data. If your Collection process is short enough, you can do a ClearCollect() to get the new info back into your Collection again after the form is submitted. If the Collection process takes too long, you can look at a Remove() and a Collect() for that one item that the user just edited.

That's how you can use a Collection as the source for your Gallery while still using the live CRM datasource for your Add/Edit form. I know you were working on getting the accurate match for the User, earlier, so... not to throw a wrench in things, but it might be cleaner (and require less custodial operations on your data) if you simply filtered the Gallery items in the same manner that you built your Collection. Just a thought.

 

Post back if this doesn't make sense.

KroonOfficeSol
Level 10

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

@MitchC3

 

Easiest way is just to filter your datasource without introducing a collection in your case. Do this

SortByColumns(
	Search(
		Filter(Accounts
			,!UserToggle.Value || ColumnToCheck = CurrUser // Here you should know the identifier in the table/list for the user and have a match in your CurrUser variable
		)
		, TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"
	)
	, "name"
	, If(SortDescending1, Descending, Ascending)
)
!UserToggle.Value // this part gives a true on the UserToggle beïng false, showing all the records
ColumnToCheck = CurrUser // this filters your datasource on the condition that the account belongs to the customer.
// the second line will take effect when the toggle is set to true, because then the first is false. 

It's as easy as that.

 

Hope this helps.

 

Paul

 

 

Community Support Team
Community Support Team

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

Hi @MitchC3,

Based on the issue that you mentioned, I think you have faced a Incompatible data type issue within your app.

The data type of item you selected within your Gallery which connects to a Collection (UserCollection) is not compatible with your Accounts Entity.

I think it is not necessary to add a Collection as a data source within your Gallery, I agree with @KroonOfficeSol's thought almost.

I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Gallery to following:

SortByColumns(
Search(
If(
UserToggle,
Filter(Accounts, Owner = LookUp(Users, 'Primary Email' = User().Email, User)),
Accounts
),
TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"
),
"name",
If(SortDescending1, Descending, Ascending)
)

Note: The Owner represents the Owner column in your Accounts Entity. In addition, you also need to add a Users entity (from your D365 CRM) as a data source within your app. The User represents the column in your Users entity.

 

Best regards,

Kris

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.
MitchC3
Level: Powered On

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

Great explanation and straight to the point. However, I think filtering the accounts without a collection may be the best way to go. Before looking at this issue, I have noticed that the gallery does not show all accounts when unfiltered by user. I think there are too many (at least 5,000) for the collection to hold. When I set the data row limit for non-delegable queries to above 1,000, it seems to get stuck loading the records.

 

Edit: It actually seems that this issue is not caused by the collection. I have tested switching it away from the collection and I believe it is due to the LookUp function, maybe the Filter function.

MitchC3
Level: Powered On

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

This works very nicely....however, the LookUp function causes a delegation warning and incomplete data set return. There are at least 5,000 accounts in our CRM, probably closer to 10,000 or 15,000. Is there any workaround to this? If the expression causes an incomplete return, then a complete return with no filtering toggle is not better, but at least provides a complete data set.

TimRohr
Level 10

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

LookUp() should be delegable. Can you post your formula as it stands now?

MitchC3
Level: Powered On

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

SortByColumns(
       Search(
               If(
                  UserToggle, 
                  Filter(Accounts, Owner = LookUp(Users, 'Primary Email' = User().Email, User)), 
                  Accounts
               ), 
               TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"
        ), 
        "name", 
        If(SortDescending1, Descending, Ascending)
)

From another reply, this formula removes the need for collection and the CurrUser variable. However, I get a delegation warning. Outside the warning, everything seems to be fully functional as expected.

 

Formula.jpg

 

Edit: According to documentation, I thought it must be the IF statement. When I highlight the warning, it tells me that the Filter part of this formula might not work correctly on large data sets.

TimRohr
Level 10

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

I'd have to do some testing, but I think when you embed the LookUp() in a Filter, or possibly when you embed the Lookup() in a Filter while simultaneously referencing properties re: the User, LookUp stops being truly delegable.

 

Try this:

Assign a variable to be the result of the LookUp() at some point prior to needing it in the Gallery (it could be in the OnVisible event of the screen, or the OnStart event of the app). Either UpdateContext({CurrUser:LookUp(....)} if you are doing it on the screen itself, or Set(CurrUser, LookUp(...)) if you are doing it in the OnStart. Then pass that variable into the Filter() statement. I think your delegation warning should go away.

MitchC3
Level: Powered On

Re: New Item property caused error: Invalid formula, expected a value compatible with DataSource

Set(CurrentUser, LookUp(Users, 'Primary Email' = User().Email, User)); Set(UserToggle, true)

 Thought this would do the trick, but I still get the warning. This is what I have to set everything and the delegation warning has moved to the "=" and ".Email" portion of this formula. However, everything else works as expected.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 88 members 4,352 guests
Please welcome our newest community members: