How to build an app to assign items to a current event (starting date - ending date) and future

[Mhashemyan]

I have 2 tables

1- Event list (Starting Date, Ending Date, ID)

1- Items (Name, Category, Starting Date, Ending Date, Event ID)

Currently I am patching the name of the event on the items table. This way I know which item is booked out according to the name of the event.

But this only works for current event, I can't assign the item to multple upcoming events.

How would I go about to solving this issue??

[wyotim · Accepted solution]

I think you are going to need a third table as you seem to be describing a many-to-many relationship between the events and items. A table comprised of event IDs and item IDs should do the trick. That way, you can map multiple items to events.

[Mhashemyan]

Thank you soo much. I wanted to ask another thing.

As the number of records might be quite high, should i be doing as collection or just patching new records?

I dont know if its quicker to load from collection and thus a better way, my only worry is if the collection get cleared or lost I would be left with no data for knowing where items are assigned to.

What woud you recommend?

[wyotim]

Forgive me if I am misunderstanding, but are you asking whether to use a collection only for the third table versus patching the third table to the data source? I would make the third table a table in the data source you are using, not a collection only, and patch to it. Then you could use that third table as a reference for viewing what items are assigned to what events.

I imagine a scenario like so: you have a list of events. Selecting an event will navigate to a screen that shows the items assigned to that event, with the ability to add or remove said items. This table would be sourced from the described third table, filtering by the event ID and showing the item information using a LookUp function from the table of items.

You could also do the reverse and have a list of items which when selected would navigate to a screen that shows all the events the item has been assigned to.

As for the amount of records being high and using a collection, using a collection is always faster than going directly from the source in my experience. So I always pull data into collections in the app. Now, assuming the number of events you need to view is between 500 and 2000 and the number of items that can be assigned is in the same range, you shouldn’t have too much trouble with collections. To keep things running fast, PowerApps defaults to pulling in 500 items and has a user configurable max of 2000 items per data source.

If either list is bigger, you will need to devise a way to pull more data into you app. If this is the case, there are methods to pull in more than 2000 items which I will be happy to share; just let me know.

Apologies if this response is a bit long. Let me know if this doesn’t address any of your questions or if you have more!

[Mhashemyan]

Thank you again for your support.

I have done what you suggested patching to a new table (Item_Booking) and I have now Events (Call_Sheet_ID) with the barcode of the item booked for the duration, patched from a collection. Like a Log.

If I can ask you support a bit more. Before I was able to display "Unavailable" by using an if statement which would compare the starting of the Event (Call_Sheet_ID) the start dates are in the grey boxes below. To the starting date of the item that I was patching on the item table as starting and ending date.

Now that I have a table and multiple bookings for the same item (Barcode) how can do the same. So searching the table above Start_Date_ID and End_Date_ID with the grey box below. To display whether it will be available or not.

Am sorry if my explanation is confusing and thank you so much for your help on setting me on right track I just can't figure this bit out.  If you can shed some light on how I can do this, It would be greatly aprreciated. Thanks again.

[wyotim]

Apologies for the late reply. I was traveling yesterday. 

I am going to describe the method I would use but unfortunately I am unable to create a test version for myself to get the logic sorted out. Normally I would make a test version so I could give code along with my thoughts but I am unable to connect data sources to apps due to an internal PowerApps error. Hopefully I will be able to get it dealt with soon so I can share some code with you!

Basically, what I would do is create a collection of event IDs whose date range conflicts with the selected event’s date range. Then in the gallery of items, I would write a statement that looks through the table of event IDs and item IDs to see if there is a match between the conflicting events and that item. If so, it would be designated as “unavailable”.

If not, I would also want to see if it is assigned to the selected event. If so, I would designate the item as “assigned” and if not, designate the item as “available”.

My apologies for being so general on this. Like I said, I am currently unable to create a test app so it does limit my ability to be as helpful as I would like. Hopefully this helps a bit though! Let me know how it goes!