Final field in sequential dependent form

[marcelo34]

Hello,
I have a “Products” table which has the following fields:
CustomerId, SkuName, List Price, Price Today

I then created a form where I first display the Clients.
Once the client is selected, the SKUNames for that client is displayed.
finally, when SKUName is selected, the price for that product/client is displayed.

The issue is, that there is always just one “Price today” value, but the form still needs me to click on it.
If I do not, the other formulas on the form don’t get activated (final price which is amount * price)
And when I do, I get an error message stating that “The entry is invalid”

Before I select the price

After I select the price

I do believe that an App Formula would be the best approach, but I could not get the following formula to work, which gets the price based on the product name and the company.
It just brings back the first price it finds.

ANY(
  SELECT (
	Products[Price Today],
	IN([_THISROW].[SkuId], 
      select(Products[SkuName], [CustomerId]=[_THISROW].[CustomerId]) )	  
  )
)

Any help would be appreciated.

Thanks

[Suvrutt_Gurjar]

I believe you are correct that the Unit Price can be a computed value with app formula based on previous field elections.

For finding the correct formula, I believe you may wish to mention a few more details.

In the expression you have mentioned fields such as [SkuId] , [Customer ID] which are not in the list of fields of “Products” table you have mentioned and Client field is not used in the expression. Could you add if these are fields of different table?

You may wish to mention details of other table, if there is one in the expression.

Are the previous fields of client, SKUNames and populated using dependent dropdown?

Is the form based on Products or different table? And since the expression is comparing [SkuId] with [SkuName], I believe you may wish to mention keys and labels of relevant tables as well.

Edit: Some edits to the description.

[marcelo34]

Hi Suvrutt,
Thanks for your reply. You are right, I was inconsistent with column names. II started the question with example column names, then in the code I used actual names. I updated the question to reflect real column names

The Orders table has the columns:
CustomerId , SkuId, amount, Unit Price, Total

So the form is to add records to the Orders table, based on information from the Products
Orders.CustomerId = Products.CustomerId
Orders.SkuID = Products.SkuName
Orders.Unit Price = Products.Price Today

So Orders.CustomerId and Orders.SkuID get populated via Dependent Dropdown.
Those 2 combination always has just one result.

I need to develop the App Formula to obtain the Products[Price Today] value for that combination

I was also playing with

SELECT (
	Products[Price Today], 
    AND( 
      ([CustomerId]=[_THISROW].[CustomerId]) , 
      ([SkuName]=[_THISROW].[SkuId]) 
   ) 
 )

To no avail…

Thanks again for helping out.

[marcelo34 · Accepted solution]

Not sure why last night it was failing, but this morning I refreshed the browser and this App Formula is working! The “ANY” is necesarry because select returns a list and the ANY function returns a single value. Since I know that the select will always return just one value, then ANY is just used to convert the list of one into a single value.

ANY(SELECT (
    Products[Price Today], 
        AND( 
          ([CustomerId]=[_THISROW].[CustomerId]) , 
          ([SkuName]=[_THISROW].[SkuId]) 
        )
     )
 )

[Suvrutt_Gurjar]

Excellent @Marcelo.

Nice to know you solved it. Also nice that you posted the solution for any future readers of this post thread.
All the best with your app