Submitting ClearCollect filtered values to a SharePoint lookup column

[PAOH]

Hi all,

With some help from the forum i've managed to filter items returned to a sharepoint lookup column when a value > 0 using the ClearCollect function

My problem is that now the data will not submit to my SharePoint lookup column.

This is the code used on my app: 

EditScreen1 - OnVisible:

ClearCollect(filteredlistB,AddColumns(Filter('Module 1 - Details',Value('Places Remaining')>0),"NewDate", Text('Course Date')))

DataCardValue1 - Items:

filteredlistB.NewDate

Before making my alterations, EditScreen1 - OnVisible was blank and DataCardValue1 - Items was:

Choices('Module 1 - Bookings'.DateOfCourse)

The problem is when i hit the submit button, an item is created in SharePoint but the lookup column in my list 'Date of Course' is blank. Any ideas?

[KroonOfficeSol]

@PAOH

The issue is on the DataCard.Update part. Here you normally give back a record/object which exists in the lookup list.

If I'm right you want to have a list off Courses which are not fully booked, am I right? But in your case you just have a list off dates and try to write back a textline instead off a record/Object.

You should do something like this:

Filter(Choices('Module 1 - Bookings'.DateOfCourse), ID in filteredlistB.ID, CourseDate > Today) 
// last in a different format I would think

So now you have a list off records/object from you related table, which are filtered on the filtered list, and later then today, which is logical in my opinion.

Offcourse you will get navigation issues when the lookup list exceeds a maximum off 2.000, but this will be the case also for you collection I believe. If not you could take the list and use this, but then you have to use 

ShowColumns(filteredlistB, "Field 1", "Identifier for your lookup list")

Then on the .Update attribute you do use a lookup formula like

LookUp(Choices('Module 1 - Bookings'.DateOfCourse), ID = Dropdown,Selected.ID)

Hope this helps,

Paul

[PAOH]

Hi Paul,

Thanks for your reply, yes that's exactly what i want to do, for now I kept it simple to test with and changed the 'Update' value of Date of Course_DataCard2 to:

Filter(Choices('Module 1 - Bookings'.DateOfCourse), ID in filteredlistB.ID)

This then shows the following error:

The lookup list will never exceed 2,000 items so that won't be a problem  

Any ideas?

[KroonOfficeSol]

You put this formula on the update attribute. You should put this on a combobox, so the user can select one off the courses. Then connect the update attribute to the combobox by using a lookup like
LookUp(Choices('Module 1 - Bookings'.DateOfCourse), ID = combobox.selected.ID)

This will update just a record.

Paul

[PAOH]

Hi Paul,

Thanks for your help,

Currently I have the following customisations:

EditScreen1 - OnVisible

ClearCollect(filteredlistB,AddColumns(Filter('Module 1 - Details',Value('Places Remaining')>0),"NewDate", Text('Course Date')))

Date Of Course_DataCard2 - Update

LookUp(Choices('Module 1 - Bookings'.'Date Of Course'), ID = DataCardValue1.Selected.ID)

this is showing the below errors:

DataCardValue1 - Items

filteredlistB.NewDate

[KroonOfficeSol]

@PAOH

Sorry for the late respons, did you figured it out already?

The problem lays in your DataCardValue1.Items, where you only have the NewDate and so no ID. Should be like this:

ShowColumns(filteredlistB, "NewDate", "ID")

Like so you have both your display field as your condition field. 

Hope this helps,

Paul