Filtering a list for a matching item

Like (1) Share

Report

Posted on 18 Aug 2024 23:53:53 by CU-19080002-10

Hi,

I am building a Quoting application in power apps for work and am stuck on a specific requirement.

I have a list of NDIS Support items in a Combo Box of a form. The list is a choices list pulled from another list.

An example of an item in the Choices list looks like the below:

Title: 02_102_0136_6_1

Support Item Name: Access Community Social and Rec Activ - Standard - Weekday Daytime

Concatinated Support Item (custom Field, name and Title): 02_102_0136_6_1 - Access Community Social and Rec Activ - Standard - Weekday Daytime

Rate: $57.60

Start Time: 9:00 AM

Finish Time: 8:00 PM

Title: 02_103_0136_6_1

Support Item Name: Access Community Social and Rec Activ - Standard - Weekday Evening

Concatinated Support Item (custom Field, name and Title): 02_102_0136_6_1 - Access Community Social and Rec Activ - Standard - Weekday Evening

Rate: $62.60

Start Time: 8:00 PM

Finish Time: 11:59 PM

All fields in the list are text fields

The requirement is to have a custom Standard item such as: 'Access Community Social and Rec Activ - Standard'.

When a user enters the Start Time and Finish Time in the form (Text input fields) for example: Start Time: 9:00 AM and Finish Time: 10:00 PM

and 'Access Community Social and Rec Activ - Standard' is selected form the dropdown. It looks through the list and finds the original Access Community Social and Rec Activ matching the times selected and adds these items to the collection.

So for example: it should find Access Community Social and Rec Activ - Standard - Weekday Daytime and Access Community Social and Rec Activ - Standard - Weekday Evening as these items fall within the times input by the user.

I have tried numerous ways to get this to work but i am not having any luck.

The current formula for adding an item to the collection is below:

Set(
ExistingItem,
LookUp(
QuoteDataCollection,
'NDIS Support Item' = NDISSupportItem.Selected.Value && Ratio = RatioSelector.Selected.Value
)
);

If(
!IsBlank(ExistingItem),
Patch(
QuoteDataCollection,
ExistingItem,
{
Hours: ExistingItem.Hours + (Value(Hours.Value) * QuoteWeeks.Value),
Total: ExistingItem.Total + Value(Gallery2_3.Selected.Total)
}
),
Collect(
QuoteDataCollection,
{
'NDIS Number': SupportItemNumber.Value,
Service: SelectedService.Value,
Day: FormattedDaysTable,
Occurrence: Occurrence.Selected,
Year: QuoteYear.Selected,
Frequency: QuoteFrequency.Selected,
'Custom Frequency': CustomFrequency.Value,
'Service Start': Text(DateValue(ServiceStart.SelectedDate)),
'Service End': Text(DateValue(ServiceEnd.SelectedDate)),
Weeks: QuoteWeeks.Value,
'Start Time': StartTimeSelector_lbl2_2.Text,
'Finish Time': FinishTimeSelector_lbl2_2.Text,
Hours: Value(Hours.Value) * QuoteWeeks.Value,
'NDIS Support Item': NDISSupportItem.Selected.Value,
Ratio: RatioSelector.Selected.Value,
Rate: Text(Value(SupportRate.Value), "[$-en-US]#0.00"),
'Custom Rate': CustomRate.Value,
'Num Staff': NumOfStaff.Value,
Desc: QuoteDesc.Value,
Assigned: AssignedStaff.Value,
Total: Text(Value(QuoteTotal.Value), "[$-en-US]#0.00"),
CompDate: DateValue1.SelectedDate,
UserMail: UserMail.Value,
Hour: HourValue1.SelectedItems,
Minute: MinuteValue1.SelectedItems
}
)
);

);

I hope this makes sense. Any help would be appreciated.

Categories:

Using Apps Using Formulas

All responses (6)

Answers (0)

CU-19080002-10 20 on 22 Aug 2024 at 04:52:08

Like (0)

Report

Filtering a list for a matching item

@WarrenBelz

Is there a specific way to approach this requirement? Unfortunately your suggestion didn't work.
WarrenBelz 147,114 Most Valuable Professional on 20 Aug 2024 at 00:36:01

Like (1)

Report

Filtering a list for a matching item

OK - can you just focus on the bit that is not working - you may need DateTimeValue instead of TimeValue
CU-19080002-10 20 on 19 Aug 2024 at 23:26:18

Like (1)

Report

Filtering a list for a matching item

Hi @WarrenBelz

Apologies again for the overload of information the previous post, I was trying to give the full picture of what I need to achieve.

In addition to adding the form data to a collection it is also submitted into a Sharepoint list OnSubmit.

To answer some of the questions below. The CompDate, Hour and Minute are just fields included to put a timestamp on the form so when its submitted you can see what date and time it was submitted (Yes these are multiple selections) . These are added to the SharePoint list but don't get used in the gallery on the Quote Page so can actually be left out of the collection but I have added them just incase they are needed at some point later in the development.

The NDIS is the OZ version.

The original formula I provided in my first post is how it functions without the additional requirement and is working as intended.

I have pasted the formula with the functionality I am trying to achieve highlighted below:

It currently is still adding the standard item to the gallery instead of finding the Weekday - Daytime and Weekday - Evening lines in the list that match the times that have been input. Once it is working I plan to clean up the formula so that the collection is updated rather than collecting all of the items again.

Set(
ExistingItem,
LookUp(
QuoteDataCollection,
'NDIS Support Item' = NDISSupportItem.Selected.Value && Ratio = RatioSelector.Selected.Value
)
);

If(
!IsBlank(ExistingItem),
Patch(
QuoteDataCollection,
ExistingItem,
{
Hours: ExistingItem.Hours + (Value(Hours.Value) * QuoteWeeks.Value),
Total: ExistingItem.Total + Value(Gallery2_3.Selected.Total)
}
),
Collect(
QuoteDataCollection,
{
'NDIS Number': SupportItemNumber.Value,
Service: SelectedService.Value,
Day: FormattedDaysTable,
Occurrence: Occurrence.Selected,
Year: QuoteYear.Selected,
Frequency: QuoteFrequency.Selected,
'Custom Frequency': CustomFrequency.Value,
'Service Start': Text(DateValue(ServiceStart.SelectedDate)),
'Service End': Text(DateValue(ServiceEnd.SelectedDate)),
Weeks: QuoteWeeks.Value,
'Start Time': StartTimeSelector_lbl2_2.Text,
'Finish Time': FinishTimeSelector_lbl2_2.Text,
Hours: Value(Hours.Value) * QuoteWeeks.Value,
'NDIS Support Item': NDISSupportItem.Selected.Value,
Ratio: RatioSelector.Selected.Value,
Rate: Text(Value(SupportRate.Value), "[$-en-US]#0.00"),
'Custom Rate': CustomRate.Value,
'Num Staff': NumOfStaff.Value,
Desc: QuoteDesc.Value,
Assigned: AssignedStaff.Value,
Total: Text(Value(QuoteTotal.Value), "[$-en-US]#0.00"),
CompDate: DateValue1.SelectedDate,
UserMail: UserMail.Value,
Hour: HourValue1.SelectedItems,
Minute: MinuteValue1.SelectedItems
}
)
);

// Step 3: Convert the input times into Time format

Set(varStartTime, TimeValue(StartTimeSelector_2.Text));
Set(varFinishTime, TimeValue(FinishTimeSelector_2.Text));

// Step 4: Filter based on Support Item Name and Time
If(
StartsWith(NDISSupportItem.Selected.Value, "Access Community") &&
EndsWith(NDISSupportItem.Selected.Value, "Standard"),

ClearCollect(
FilteredItems,
Filter(
'Price List 2024-25',
'Support Item Name' = "Access Community Social and Rec Activ - Standard" &&
(
(varStartTime >= StartTime && varStartTime <= EndTime) ||
(varFinishTime >= StartTime && varFinishTime <= EndTime) ||
(varStartTime < StartTime && varFinishTime > EndTime)
)
)
);

// Step 5: Collect the Filtered Items into QuoteDataCollection
ForAll(
FilteredItems,
Collect(
QuoteDataCollection,
{
'NDIS Number': SupportItemNumber.Value,
Service: SelectedService.Value,
Day: FormattedDaysTable,
Occurrence: Occurrence.Selected,
Year: QuoteYear.Selected,
Frequency: QuoteFrequency.Selected,
'Custom Frequency': CustomFrequency.Value,
'Service Start': Text(DateValue(ServiceStart.SelectedDate)),
'Service End': Text(DateValue(ServiceEnd.SelectedDate)),
Weeks: QuoteWeeks.Value,
'Start Time': StartTimeSelector_lbl2_2.Text,
'Finish Time': FinishTimeSelector_lbl2_2.Text,
Hours: Value(Hours.Value) * QuoteWeeks.Value,
'NDIS Support Item': ThisRecord.'Support Item Name', // Use the filtered item's Support Item Name
Ratio: RatioSelector.Selected.Value,
Rate: Text(Value(SupportRate.Value), "[$-en-US]#0.00"),
'Custom Rate': CustomRate.Value,
'Num Staff': NumOfStaff.Value,
Desc: QuoteDesc.Value,
Assigned: AssignedStaff.Value,
Total: Text(Value(QuoteTotal.Value), "[$-en-US]#0.00"),
CompDate: DateValue1.SelectedDate,
UserMail: UserMail.Value,
Hour: HourValue1.SelectedItems,
Minute: MinuteValue1.SelectedItems
}
)
);

// Step 6: Reset the ComboBox and relevant controls
Reset(NDISSupportItem);
);

// Set FormattedDaysTable and submit the form
Set(FormattedDaysTable, ForAll(QuoteDay.SelectedItems, { Value: Value }));

// Notify user of success (optional)
Notify("Data has successfully been added to the Quote", NotificationType.Success);

// Reset the form and controls (optional)
ResetForm(QuoteDataForm);
Reset(StartTimeSelector_2);
Reset(FinishTimeSelector_2);
Reset(Hours);
WarrenBelz 147,114 Most Valuable Professional on 19 Aug 2024 at 08:02:27

Like (1)

Report

Filtering a list for a matching item

Hi CU-19080002-10,

Sorry, I simply cannot wade through all of that (and your screenshots are too small to read), Did you have a look at the specific questions I asked.
CU-19080002-10 20 on 19 Aug 2024 at 01:49:50

Like (1)

Report

Filtering a list for a matching item

Hi @WarrenBelz

Apologies as this was my first post. Please see details below.

Quote Data Form:
This is the functionality of the form as it stands without the added Standard item.

A user enters the times and selects the item. the rate is auto-populated and the calculations are done to get the total. this form data is then collected in the QuoteDatacollection for use in the quote page in a gallery.

Quote Page:
Specific items from the collection are populated into a gallery.

The lists:
This is the list with the Support items.

This is the list the Quote Data goes into from the form with the Choices column from the Price List (Sample data included).

When selecting the standard item

I need to perform a lookup for the support items that match the times input by the user. Then replace the items in the collection with the found items (Weekday Daytime and Weekday Evening). The rate input in the form will show N/A as there are now two rates found and each rate will be calculated in the Collection. If functioning correctly, the quote page should sow both items:

I have tried different variants of the following but the items are not showing in the Quote page, it just shows the standard item with no rate or total.

Set(
ExistingItem,
LookUp(
QuoteDataCollection,
'NDIS Support Item' = NDISSupportItem.Selected.Value && Ratio = RatioSelector.Selected.Value
)
);

If(
!IsBlank(ExistingItem),
Patch(
QuoteDataCollection,
ExistingItem,
{
Hours: ExistingItem.Hours + (Value(Hours.Value) * QuoteWeeks.Value),
Total: ExistingItem.Total + Value(Gallery2_3.Selected.Total)
}
),
Collect(
QuoteDataCollection,
{
'NDIS Number': SupportItemNumber.Value,
Service: SelectedService.Value,
Day: FormattedDaysTable,
Occurrence: Occurrence.Selected,
Year: QuoteYear.Selected,
Frequency: QuoteFrequency.Selected,
'Custom Frequency': CustomFrequency.Value,
'Service Start': Text(DateValue(ServiceStart.SelectedDate)),
'Service End': Text(DateValue(ServiceEnd.SelectedDate)),
Weeks: QuoteWeeks.Value,
'Start Time': StartTimeSelector_lbl2_2.Text,
'Finish Time': FinishTimeSelector_lbl2_2.Text,
Hours: Value(Hours.Value) * QuoteWeeks.Value,
'NDIS Support Item': NDISSupportItem.Selected.Value,
Ratio: RatioSelector.Selected.Value,
Rate: Text(Value(SupportRate.Value), "[$-en-US]#0.00"),
'Custom Rate': CustomRate.Value,
'Num Staff': NumOfStaff.Value,
Desc: QuoteDesc.Value,
Assigned: AssignedStaff.Value,
Total: Text(Value(QuoteTotal.Value), "[$-en-US]#0.00"),
CompDate: DateValue1.SelectedDate,
UserMail: UserMail.Value,
Hour: HourValue1.SelectedItems,
Minute: MinuteValue1.SelectedItems
}
)
);
// Step 3: Convert the input times into Time format
Set(varStartTime, Time(Value(Left(StartTimeSelector_lbl2_2.Text, 2)), Value(Mid(StartTimeSelector_lbl2_2.Text, 4, 2)), 0));
Set(varFinishTime, Time(Value(Left(FinishTimeSelector_lbl2_2.Text, 2)), Value(Mid(FinishTimeSelector_lbl2_2.Text, 4, 2)), 0));
// Step 4: Filter based on Support Item Name and Time
If(
StartsWith(NDISSupportItem.Selected.Value, "Access Community") &&
EndsWith(NDISSupportItem.Selected.Value, "Standard"),
ClearCollect(
FilteredItems,
Filter(
'Price List 2024-25',
'Support Item Name' = "Access Community Social and Rec Activ - Standard" &&
(
(varStartTime >= StartTime && varStartTime <= EndTime) ||
(varFinishTime >= StartTime && varFinishTime <= EndTime) ||
(varStartTime < StartTime && varFinishTime > EndTime)
)
)
);
// Step 5: Collect the Filtered Items into QuoteDataCollection
ForAll(
FilteredItems,
Collect(
QuoteDataCollection,
{
'NDIS Number': SupportItemNumber.Value,
Service: SelectedService.Value,
Day: FormattedDaysTable,
Occurrence: Occurrence.Selected,
Year: QuoteYear.Selected,
Frequency: QuoteFrequency.Selected,
'Custom Frequency': CustomFrequency.Value,
'Service Start': Text(DateValue(ServiceStart.SelectedDate)),
'Service End': Text(DateValue(ServiceEnd.SelectedDate)),
Weeks: QuoteWeeks.Value,
'Start Time': StartTimeSelector_lbl2_2.Text,
'Finish Time': FinishTimeSelector_lbl2_2.Text,
Hours: Value(Hours.Value) * QuoteWeeks.Value,
'NDIS Support Item': ThisRecord.'Support Item Name', // Use the filtered item's Support Item Name
Ratio: RatioSelector.Selected.Value,
Rate: Text(Value(SupportRate.Value), "[$-en-US]#0.00"),
'Custom Rate': CustomRate.Value,
'Num Staff': NumOfStaff.Value,
Desc: QuoteDesc.Value,
Assigned: AssignedStaff.Value,
Total: Text(Value(QuoteTotal.Value), "[$-en-US]#0.00"),
CompDate: DateValue1.SelectedDate,
UserMail: UserMail.Value,
Hour: HourValue1.SelectedItems,
Minute: MinuteValue1.SelectedItems
}
)
);
// Step 6: Reset the ComboBox and relevant controls
Reset(NDISSupportItem);
);
// Set FormattedDaysTable and submit the form
Set(FormattedDaysTable, ForAll(QuoteDay.SelectedItems, { Value: Value }));
// Notify user of success (optional)
Notify("Data has successfully been added to the Quote", NotificationType.Success);
// Reset the form and controls (optional)
ResetForm(QuoteDataForm);
Reset(StartTimeSelector_2);
Reset(FinishTimeSelector_2);
Reset(Hours);

I hope this is clear. Apologies if its a lot of information
WarrenBelz 147,114 Most Valuable Professional on 19 Aug 2024 at 00:35:49

Like (2)

Report
Filtering a list for a matching item

Hi CU-19080002-10

I am missing a lot of information to help you properly as I cannot see your data structure or control types. A suggestion below regarding getting rid of the Variable, however this would not be your issue. You also did not say what was not working (what is the result of running this code). I have highlighted a number of things - the yellow is a suggestion on the date if you are storing it as Text. The blue is a number of queries
.Selected suggests you are writing to a Table field - is this correct
.SelectedItems suggests you have multiple selections
You have CompDate as a Date field, but 'Service Start' and 'Service End' as Text fields

With( { _Existing: LookUp( QuoteDataCollection, 'NDIS Support Item' = NDISSupportItem.Selected.Value && Ratio = RatioSelector.Selected.Value ) }, If( !IsBlank(_Existing), Patch( QuoteDataCollection, _Existing, { Hours: _Existing.Hours + (Value(Hours.Value) * QuoteWeeks.Value), Total: _Existing.Total + Value(Gallery2_3.Selected.Total) } ), Collect( QuoteDataCollection, { 'NDIS Number': SupportItemNumber.Value, Service: SelectedService.Value, Day: FormattedDaysTable, Occurrence: Occurrence.Selected, Year: QuoteYear.Selected, Frequency: QuoteFrequency.Selected, 'Custom Frequency': CustomFrequency.Value, 'Service Start': Text(ServiceStart.SelectedDate, "dd/mm/yyyy"), 'Service End': Text(ServiceEnd.SelectedDate, "dd/mm/yyyy"), Weeks: QuoteWeeks.Value, 'Start Time': StartTimeSelector_lbl2_2.Text, 'Finish Time': FinishTimeSelector_lbl2_2.Text, Hours: Value(Hours.Value) * QuoteWeeks.Value, 'NDIS Support Item': NDISSupportItem.Selected.Value, Ratio: RatioSelector.Selected.Value, Rate: Text(Value(SupportRate.Value), "[$-en-US]#0.00"), 'Custom Rate': CustomRate.Value, 'Num Staff': NumOfStaff.Value, Desc: QuoteDesc.Value, Assigned: AssignedStaff.Value, Total: Text(Value(QuoteTotal.Value), "[$-en-US]#0.00"), CompDate: DateValue1.SelectedDate, UserMail: UserMail.Value, Hour: HourValue1.SelectedItems, Minute: MinuteValue1.SelectedItems } ) ) );

Can you please confirm exactly what the error/non function is ? BTW - I assume NDIS is the Oz version.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee