Solved: Final field in sequential dependent form - Page 2

marcelo34 · 11-17-2020 04:45 PM

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

marcelo34

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]) 
        )
     )
 )

View solution in original post