Issue
Using a Forms control to collect information from the end-user in a canvas app is a best practice. However, it is not often straightforward to display the data in a combo box, and this is because of certain limitations of the combo box – control itself. Let us explain the issue.

Overview of the resources used to demonstrated and explain the issue:

  • Three SharePoint lists
    • Locations
    • Events
    • Registrations
  • Canvas App with Form control and two icons (Save and Reload) 

The events – and registrations list have a lookup to the locations list to get the location names. The registrations list has a lookup to the events list to get the dates of the events. 

SharePoint Lists

Canvas app

Display the date values in a combo-box.

In the form, the “date” depends on the selected “location“. When using the “Depends on…” – option of the combo box, with the parent control “DataCardValue_Location – Value” and the matching field “Events – Location“. Will create a filter in the items property of the combo box, with an error.

Auto create depends on filter error

Changing the code to “Filter(Events, Location.Value = DataCardValue_Location.Selected.Value)” resolves the error, but dates are not displayed when the location is selected.

When we double-check the values of the field results, the “Date” column does not appear as a selectable column. The screenshot illustrates the issue.

Solution
How can we resolve this issue, that the form can display the correct dates, filtered based on the location and that the end-user can select multiple dates at once?

First step
We need to create a new table based on the filtered data and collect the event id and date value. To display the date correctly in the combo box, we need to convert the value to a text format. The text format can display the dates in the combo box. 

ForAll(
    Filter(Events, Location.Value = DataCardValue_Location.Selected.Value),
    {
        EventId: ID, 
        DateValue: Date,
        DateDisplay: Text(Date)
    }
)

This result in the following result:

Second step
Nevertheless, this is not yet displaying the dates in the combo box. To display the correct data, we need to change the fields data “DisplayFields” and “SearchFields” to the correct value.

Result

Below observe the result of the changes we made.


The result of the registration is not yet as expected. Saving the date value in the registration list is not yet completed. Remember that we have a link with the events list. The problem here is that we no longer have the Choices function that returns a table of the possible values from a lookup column.
How can we resolve this second problem?

Saving the dates values in the SharePoint list.

First step

We are adding an extra combo box in “Date_DataCard”.

We are adding an extra combo box in “Date_DataCard”.

Second step: We are copying the value of the items from DataCardValue_Date and pasting it into the items’ value for cmb_Date.

Third step: Replace the value of the items from DataCardValue_Date with the default value.

Fourth step: Replace the value of the DefaultSelectedItems from DataCardValue_Date with the following code:

Filter(
    Choices(Registration.Date),
    Id in cmb_Date.SelectedItems.EventId
)

Fifth step: Hide the DataCardValue_Date in the form (Set visible = false)

Test the submission of the form result in listing the dates in the SharePoint list “registrations”. Let us hope this tutorial helps when using a multi-selection combo box with dates. Like and share this blog as much as wanted. Thanks “registrations”.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: