I'm trying to make 10 text boxes that will display the value for any one of ~30 metrics for the first 10 entities selected in a list box.
My data contains Region, Area, Store, Sales, (and 30 other metrics). If a user selects 10 Stores, I would like the text boxes to display the name of the ten stores selected as well as the values for the selected metric.
For a simple example, if two stores are selected (Store1 and Store2), and the selected metric is Sales, then I would like the first two text boxes to show the sales for Store1 and Store2 respectively. I am using variables to get the store names that are selected:
vEntity1 = subfield(GetFieldSelections(Store_Name,',',20),',',1)
vEntity2 = subfield(GetFieldSelections(Store_Name,',',20),',',2)
I've managed to get the text boxes to show the names, but now I am struggling to find an easily scalable way to get them to also display the values for any single metric that is selected (in a list box). To display the Sales I currently have a separate Metric Definition for each text box stored in a table.
The first two text boxes then have the formula '=$(=Metric_Definition1)' and '=$=(Metric_Definition2)'.
When Sales is selected then the text boxes show the sales for the two selected Stores. If another metric is chosen, or other stores are selected then the values update.
This works, but since with this method I need to make 10 definitions for each metric (30+ metrics total), and I'd like to be able to select different Entity granularities (Region or Area as well), this will result in over 300 relatively complex metric definitions - lots of scope for things to break.
I'm wondering if anyone might have a better more streamlined way of doing this.
- Select some stores
- Select a metric
- Have text boxes displaying the store name and metric value
Many thanks for any help - you will be saving me from a lot of headaches!