Hi everyone,
I'm currently in the process of designing a power app made for tracking inventory. It's designed in such a way that each change in stock is tracked in a excel tab called mutations, and these mutations are evaluated in an other excel tab to display the actual stock. All of this works fine. The app has 2 main comboboxes, where the user can select products and locations. There's a toggle to alter between in and outgoing stock for that location. At the end there's a combobox to track the name of the user and a submit button which patches the content to my excel sheet.
Each mutation ends up where it's supposed to, and the actual stock is calculated right via some excel formulas. The problem however, lies in displaying the actual stock in app.
Since it's a inventory tracking app, it is important the most updated data is available in app. I display the data using a HTML text box.
The main problem is that when I select a product and no location, the html box displays the stock per location of this product correctly. However, if I select only a location, so no product, the html box displays the location as having no stock.
This only happens when I've just submitted a new mutation through the app. After a 1-2 minutes the problem solves itself automatically, and the newest information is displayed.
My question is mainly in how I can solve this. I've tried everything from refreshing the table in the onchange of each combobox, to rewriting the html code to change the order of the cases I've set up. What baffles me is that the newest information is visible in one way, but not the other, although both ways get the info from the same table.
The HTML code is as follows:
If(
// CASE 0: both location and product selected
!IsBlank(CmbLocation.Selected.Locations) && !IsBlank(CmbProducts.Selected.Products),
With(
{
rec: First(
Filter(
TblActualStock_Calc,
Location = CmbLocation.Selected.Locations
)
),
selProd: CmbProducts.Selected.Products
},
"<table style='width:100%; font-family:Open Sans; font-size:26px; border-collapse:collapse;'>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Location:</td>
<td style='padding:10px;'>" &
CmbLocation.Selected.Locations &
"</td>
</tr>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Product:</td>
<td style='padding:10px;'>" &
selProd &
"</td>
</tr>
<tr>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Current stock:</td>
<td style='padding:10px;'>" &
If(
// location empty / no product stored → no stock
IsBlank(rec) Or rec.Product = "" Or rec.Product = "None",
"No stock available at this location",
// CASE 5: a product is stored here, but it is not the selected product → mismatch
rec.Product <> selProd,
"This product is not stored at this location. Current product here is " & rec.Product & ".",
// CASE 4: valid combination → show stock
Text(rec.'Current stock')
) &
"</td>
</tr>
</table>"
),
// CASE 1: only location selected
If(
!IsBlank(CmbLocation.Selected.Locations),
With(
{
rec: First(
Filter(
TblActualStock_Calc,
Location = CmbLocation.Selected.Locations
)
)
},
"<table style='width:100%; font-family:Open Sans; font-size:26px; border-collapse:collapse;'>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Location:</td>
<td style='padding:10px;'>" &
CmbLocation.Selected.Locations &
"</td>
</tr>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Product:</td>
<td style='padding:10px;'>" &
If(
// no record found or product is empty → nothing stored here
IsBlank(rec) Or rec.Product = "" Or rec.Product = "None",
"No product assigned to this location",
rec.Product
) &
"</td>
</tr>
<tr>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Current stock:</td>
<td style='padding:10px;'>" &
If(
// no record found or empty stock → no stock available
IsBlank(rec) Or rec.'Current stock' = "" Or rec.'Current stock' = "None",
"No stock available",
Text(rec.'Current stock')
) &
"</td>
</tr>
</table>"
),
// CASE 2: only product selected
If(
!IsBlank(CmbProducts.Selected.Products),
With(
{
prodName: CmbProducts.Selected.Products,
locRecords: Filter(
TblActualStock_Calc,
Product = CmbProducts.Selected.Products && 'Current stock' <> "None"
),
totalStock: Sum(
Filter(
TblActualStock_Calc,
Product = CmbProducts.Selected.Products && 'Current stock' <> "None"
),
Value('Current stock')
)
},
"<table style='width:100%; font-family:Open Sans; font-size:26px; border-collapse:collapse;'>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Product:</td>
<td style='padding:10px;'>" &
prodName &
"</td>
</tr>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Locations:</td>
<td style='padding:10px;'>" &
If(
// no matching rows found → product is not stored anywhere
CountRows(locRecords) = 0,
"This product is not stored at any location",
// product exists → show all locations and stock
Concat(
locRecords,
Location & " (" & 'Current stock' & ")",
"<br/>"
)
) &
"</td>
</tr>
<tr>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Total stock:</td>
<td style='padding:10px;'>" &
If(
// no stock anywhere → show message
CountRows(locRecords) = 0,
"No stock available",
// otherwise show total stock
Text(totalStock)
) &
"</td>
</tr>
</table>"
),
// CASE 3: neither location nor product selected
"<table style='width:100%; font-family:Open Sans; font-size:26px; border-collapse:collapse;'>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Location:</td>
<td style='padding:10px;'>No location selected</td>
</tr>
<tr style='border-bottom:2px solid #0059B2;'>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Product:</td>
<td style='padding:10px;'>No product selected</td>
</tr>
<tr>
<td style='font-weight:bold; padding:10px; width:140px; border-right:2px solid #0059B2;'>Current stock:</td>
<td style='padding:10px;'>No stock shown</td>
</tr>
</table>"
)
)
)
I am getting kind of desperate in getting this fixed, every help is welcome!!
