cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biterbit
Advocate IV
Advocate IV

Writing a value from a ComboBox based on a Collection results in a Null value in the SharePoint list Lookup field

I have an app which has been working for a while where the user can edit and add records to a Sharepoint list (called 'Sessions'). The Sharepoint list contains a few Lookup fields, one of which (called 'Client') refers to another list (called 'Client data'), using the field 'Reference' . The field does not allow multiple values.

The ComboBox for the Lookup field has an Items formula of: 

 

 

Choices([@Sessions].Client)

 

 

 

and the field's datacard has an Update formula of:

 

 

DataCardValue.Selected

 

 

 

This works fine, but the list of value in the ComboBox is very large and so I created a Collection (called 'MyClients') which filters out the 'Client data' list records which are relevant to the current user (usually only 4 or 5 records). I changed the ComboBox Items formula to:

 

 

MyClients.Reference

 

 

 

and this correctly shows only the required 4/5 values in the ComboBox list. Everything looks good until the form is submitted: the resulting (new) record is created but with no value in the 'Client' field. I have spend several hours playing around with the Items and Update formulas in the hope of finding a solution, but without success.

 Does anyone know why using a ComboBox value selected from the Collection rather than the underlying table will not work?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@biterbit 

Adding this to a collection is not an avoidance of delegation or record limit.  Your formula is delegable, so record limit will be the only concern (which again, a collection will not solve in this case).  

 

Change your Items property formula to the following:

ForAll(
    Filter('Client data',Counsellor.Value = User().FullName),
    {Id : ID,
     Value: Reference
    }
)

 

This will give you a list of client data based on the current counsellor. 

It will also shape the records into the appropriate record schema for updating your Lookup column in your primary list in the form.

Make sure your Update property on the datacard still references this combobox properly.

   datacardvaluex.Selected

 

 

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

View solution in original post

4 REPLIES 4
RandyHayes
Super User III
Super User III

@biterbit 

The issue is more with how you are creating your collection.  You don't need a heavyweight collection for what you are doing, you just need to filter at the Items property of the combobox.

 

So, how are you collecting data to the collection?

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

Thanks for your response. I used a collection because I only need those records relevant to the current user (I was concerned about delegation limits). I create the collection using when the first screen loads, using a Refresh button OnSelect function (I tried using the App OnStart but this did not always seem to work):

 

ClearCollect(MyClients,Filter('Client data',Counsellor.Value = User().FullName))

 

This gets me a list of the Client records relevant to the current user, from which they can pick to create a reference to that Client in a new Session record. If you think this approach won't work then I might be able to design the solution a different way: but I'd still be interested to understand what the issue is for future development work.

Thanks.

RandyHayes
Super User III
Super User III

@biterbit 

Adding this to a collection is not an avoidance of delegation or record limit.  Your formula is delegable, so record limit will be the only concern (which again, a collection will not solve in this case).  

 

Change your Items property formula to the following:

ForAll(
    Filter('Client data',Counsellor.Value = User().FullName),
    {Id : ID,
     Value: Reference
    }
)

 

This will give you a list of client data based on the current counsellor. 

It will also shape the records into the appropriate record schema for updating your Lookup column in your primary list in the form.

Make sure your Update property on the datacard still references this combobox properly.

   datacardvaluex.Selected

 

 

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

View solution in original post

Thanks Randy. I understand your point about delegation. I guess that pulling the data back once might be more efficient than several database round-trips, but your solution works fine. Thanks for your help.

Helpful resources

Announcements
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

secondImage

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

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (18,934)