cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Combining two tables SQL tables into one collection

1. Is it possible to take a series of fixed time slots (Time_Slot_Collection): 07:00-08:00, 08:00-09:00, .... 15:00-16:00

 

2. And fill in the Door# and Load# for just the booked appointments (Booked Collection):

Date Time_Slot Door Load# Bay

20190926 07:00-08:00 1 ABC123 F-Bay

20190926 07:00-08:00 2 BCD456 F-Bay

20190926 10:00-11:00 1 EFG678 F-Bay

 

3. So the resulting collection looks like

Date          Time_Slot    Door    Load#         Bay

20190926 07:00-08:00    1        ABC123     F-Bay

20190926 07:00-08:00    2        BCD456     F-Bay

20190926 08:00-09:00                                F-Bay

20190926 09:00-10:00                                F-Bay

20190926 10:00-11:00     1       EFG678      F-Bay

20190926 11:00-12:00                                 F-Bay

20190926 12:00-13:00                                F-Bay

20190926 13:00-14:00                                F-Bay

20190926 14:00-15:00                                F-Bay

20190926 15:00-16:00                                F-Bay

 

3. I've attempting using Add columns, but the Lookup will fail since there are two appointments at 07:00-08:00. Snippet of code below

 

4. Any ideas? I've attempting using a series of direct lookups against the table in SQL Server / Azure, but results in error message: "Server Response. Rate Limit is Exceeded. Try again is 24 seconds"

 

ClearCollect(Door_Schedule_Collection, AddColumns( Time_Slot_Collection, "BookDateInt", Lookup(Booked_Collection,BookDateInt = Value(Text(Book_Date_CAL.SelectedDate,"[$-en-US]yyyymmdd")),BookDateInt), "Bay", Lookup(Booked_Collection,BookDateInt = Value(Text(Book_Date_CAL.SelectedDate,"[$-en-US]yyyymmdd")),Bay ), "Door1", If(IsBlank(LookUp(Booked_Collection,Value(Door) = 1 && Time_Slot = Time_Slot_Collection[@Time_Slot]))," ", LookUp(Booked_Collection,Time_Slot = Time_Slot_Collection[@Time_Slot],Door) ),

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Combining two tables SQL tables into one collection

Hi @BrewinBruin ,

Could you please share a bit more about your scenario?

Do you want to combine the time slots collection with the Booked collection based on the Time_Slot value?

 

Based on the formula you provided, I think there is something wrong with it. I have made a test on my side, please take a try with the following workaround:

Add a button called "Merge Together", then set the OnSelect property to following:

ClearCollect(Booked_Collection, '[dbo].[YourBookedSQLTable]');    /* <-- I assume that the Booked_Collection is populated from your Booked SQL Table */
ForAll( Time_Slot_Collection,
If(
Not(Time_Slot in Booked_Collection.Time_Slot),
Collect(
Booked_Collection,
{
Date: 20190926, /* <-- Date represents the Date column in your Booked_Collection, on your side, you may type "BookDateInt" */
Time_Slot: Time_Slot_Collection[@Time_Slot],
Door: Blank(),
Load#: Blank()
}
)
) );
ClearCollect(
Door_Schedule_Collection,
SortByColumns(Booked_Collection, "Time_Slot", Ascending)
)

or

ClearCollect(Booked_Collection, '[dbo].[YourBookedSQLTable]');
ForAll( Time_Slot_Collection,
If(
Not(Time_Slot in Booked_Collection.Time_Slot),
Collect(
Booked_Collection,
{
Date: Value(Text(Book_Date_CAL.SelectedDate, "[$-en-US]yyyymmdd")), /* <-- Date represents the Date column in your Booked_Collection, on your side, you may type "BookDateInt" */
Time_Slot: Time_Slot_Collection[@Time_Slot],
Door: Blank(),
Load#: Blank()
}
)
) );
ClearCollect(
Door_Schedule_Collection,
SortByColumns(Booked_Collection, "Time_Slot", Ascending)
)

 

Then add a Data Table control within your app, set the Items property to following:

Door_Schedule_Collection

and enable corresponding columns within this Data Table.

 

When you press the above "Merge Together" button, the merged collection would be listed within above Data Table control.

 

Please take a try with above solution, check if the issue is solved.

 

More details about the ForAll function, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-forall

 

Best regards,

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.
Highlighted
Frequent Visitor

Re: Combining two tables SQL tables into one collection

Thanks for code snippet.  I'm close to the solution, however still need to "tweak" it so that the time slot appears for both doors.  Here's what current code is displaying:

Door_Booked_Vers1.JPG

Here's the current code:

ClearCollect(Time_Slot_Collection,ShowColumns('[dbo].[Time_Slot_Hourly]',"Time_Slot"));
ClearCollect(F_Booked_Collection,Filter('[dbo].[Booked]',Bay = "F-Bay" && BookDateInt = Value(Text(Book_Date_CAL.SelectedDate,"[$-en-US]yyyymmdd"))));
ForAll(Time_Slot_Collection,
If(Not(Time_Slot in F_Booked_Collection.Time_Slot),
Collect(F_Booked_Collection,
{ BookDateInt: Value(Text(Book_Date_CAL.SelectedDate, "[$-en-US]yyyymmdd")), 
Time_Slot: Time_Slot_Collection[@Time_Slot],  Door: Blank(),   Bay: "F-Bay",
Load_Number: Blank()}))
);

 

In addition to the "F-Bay" above, I have an additional 5 bays each with from 1 to 6 doors.  Here's a picture of my ultimate solution:

Door_Booked_Goal.JPG

Thanks for the assistance!

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,312)