updating all colums from one datasource with If statemant of other datasource

(0) Share

Report

Posted on by high-solutions

hello all,

I just started with power apps and I got stuck with comparing two datasources.

I have a "rental" datasource and a "product" datasource (from different excel sheets).

What I would like to do is compare alle dates from the rental datasource with today.

if today has passed I want the rented items back in the "product" datasource.

My plan is to do this in the Homescreen OnVisible.

Rental datasource example:

ProductId	RentUntil	howmany_Items_rented	rented_by
6	08-02-2022	5	me
6	23-02-2022	1	someOneElse
8	07-02-2022	3	me

Product datasource example:

ProductId	total_Items	items_left
6	15	9
8	3	0

So in above example I need to link the "ProductId" and the "howmany_Items_rented" with the product in the "product" database but only if the date has passed. The "rental" datasource should stay how it is so it should not be editted.

I have no idea how to do this....

It would be easier if it was one datasource I could figure it out with an ForAll function but i'm just lost here.

Can some one point me in the right direction?

Categories:

Building Power Apps

All responses (12)

Answers (2)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@high-solutions

Since a ForAll is a table producing function, it would provide the table that you want to patch to the product datasource.

However, I cannot provide a suggestion formula as you have two items in the product datasource that I am not sure where you derive them.

That is the total_items and the items_left columns. Where does that derive from?
high-solutions 26 on at

Like
a
(0)

Report
Copy link

Link copied!

the total_items is a static number never to be changed, it's only a check how many items there are, rented or not and you can't rent more items then physical possible.
"Items_left" column is the same as "total_items" only in the form when I rent something reduces this, so one can rent seperate items or all items left in stock at once.

So the "howmany_items_rented" From the rental tab have to go to the "items_left" column from the Products column when the date has passed
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@high-solutions

So then you are essentially stating that you HAVE records in your product table that need to be updated!?

My initial interpretation of your post was that you wanted to create these items in your product table if they were expired from the other.

Please clarify this part because I am not understanding what you want in the Product table, and what would denote a product being "put back" into the product table.
high-solutions 26 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes i'm trying to update "items_left" from the product datasource without altering the rentall datasource.

So the "how_many_items_rented" from productId 8 should "go back to"/copy to products datasource productId 8 "items_left"
high-solutions 26 on at

Like
a
(0)

Report
Copy link

Link copied!

to simplify the question:
How do I update a (products) datasource column with an if statement from another datasource (rentall)
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@high-solutions

Okay, but one key question that I had in the last response - "you HAVE records in your product table that need to be updated"

That is the case and correct? The only thing you are putting into the product list is the value of the "how_many_items_rented" column from the rentals back into the existing value of the record for that product in your produce table?
high-solutions 26 on at

Like
a
(0)

Report
Copy link

Link copied!

yes exactly that, and that for all items/productId's who's date is expired.

Sorry that i'm so unclear and sorry for the trouble.
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@high-solutions

I believe in general you were clear, but there were some specifics I needed to have in order to provide a suggestion.

The one challenge you have with this is that it is Excel based. So, performance will never be great with Excel, and there is more work to do for it.

Consider the following formula to do what you need:

Patch(ProductDataSource, ForAll( Filter(RentalDataSource, entUntil < Today()) As _expired, With({_item: LookUp(ProductDataSource, ProductId = _expired.ProductId)}, Patch(_item, {items_left: _item.items_left + _expired.howmany_items_rented}) ) ) )

Keep in mind that this will continue to increment the items left each time you run it because you are not indicating in your rental that the process has been "reverted"/added back.
Verified answer

PowerRanger 3,458 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@high-solutions

You mentioned: if today has passed I want the rented items back in the "product" datasource.

So you could do something like that. For testing purpose, add a DatePicker to your screen so you can play with the date. My Code uses a DatePicker to mimic "today"

ClearCollect(colRentals,GroupBy(Filter(ProductRentals,RentUntil > dtpAvailabilityDate.SelectedDate),"ProductID","myGroup")); Patch( Products, ForAll( colRentals As rentals, With( { _item: LookUp( Products, ID = rentals.ProductID ) }, Patch( _item, { items_left: _item.total_items - Sum( rentals.myGroup, how_many_items_rent ) } ) ) ) );

Using this code your Products DataBase wil be updated based on the selected Date. So you could even re-calculate items_left on dates in the past or in the future.

Please click Accept as solution 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 Thumbs Up.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@PowerRanger

Your ForAll's are starting to look better! 😁

It appears that the formula I provided before resolve the issue that @high-solutions was having.

The biggest challenge is that there is NO indication in the rentals list to indicate that the "return to Product" action has occurred. So, if the formula is evaluated again at another point, it would return the same amount again to the Product table!