Calculate total of values that contain a specific value

(0) Share

Report

Posted on by Mirtemir

242

I have two SharePoint lists: AssetOrder and Inventory with an Powerapp.

Inventory has 4 columns: Category, article, inUse (Laptops, HP Elitebook, yes).

I have 3 laptops in total.

Two of them are in use (inUse: yes).

So one of them is available (InUse: no)

In the AssetOrderlist i would like add a Column named Available and show that one laptop is available in the category inUse in Inventory. Is this possible and how?

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

notj 782 on at

Like (1)

Report
Copy link

Link copied!

@Mirtemir This covers what you're looking for: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-counts

Was this reply helpful? Yes No

rsaikrishna

3,703 Microsoft Employee on at

Like (0)

Report

Copy link

Link copied!

@Mirtemir

What is the difference between InUse (Yes/No) and Available? If InUse is No, does it means Available?

You can always add columns to SP list and adjust your Power App.

Regards

Krishna Rachakonda

If this reply helped you to solve the issue, please mark the post as Accepted Solution. Marking this post as Accepted Solution, will help many other users to use this post to solve same or similar issue without re-posting the issue in the group. Saves a lot of time for everyone.

Was this reply helpful? Yes No

Verified answer

CU-18081211-6 9,272 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi @Mirtemir ,
From my understanding, you want that every time you update InUse column values of one or more records from Inventory list, the Available column from AssestOrder of the same product category to be updated with the sum of in use article of the same category.
Well, depends on how you choose to update values in Inventory list.
There are 2 options, as least I could think of now :
1. You choose to create a Sharepoint - PowerApps form:
In this scenario you need to update OnSuccess property of the Form to something like (of course you need to adapt the bold to your app):
Patch ( AssetOrder,
Coalesce(
LookUp(AssetOrder,category = CurrentModifiedCategory,
Defaults(AssestOrder)
),
{available:CountRows(Filter(Inventory,category=CurrentModifiedCategory && !inUse))}
)
2. You choose to use the native Sharepoint form or the quick edit:
In this scenario you need to construct a PowerAutomate Flow triggered by "SharePoint - When a record is created/modified"
If you are interested in this option it time for you to discover POWERAUTOMATE as well, which in by the way a very very powerfull tool.

Was this reply helpful? Yes No