LinkedIn
Twitter
Hi everyone,
How would be possible to to select ENUM button based on calculation result between [on site] and [critical level]?
I am trying to select a button based on calculation , mentioned above.
The calculation should as
If on site amount is less then critical level select ORDER. Otherwise show Seems Good.
What formula may i use in initial value to select button automatically ?
Thanks in advance.
Solved
2 ACCEPTED SOLUTIONS
Ha, ha yes the problem is far more tricky than appears. Essentially what the app is doing is checking the current stock levels against the critical stock level at which point a new order must be placed.
Things like Minimum Stock Level, Critical Stock Level are static and only set infrequently and not with any order. The client and supplier will have agreed on these and so they are mostly static.
The On-Site Stock level is the current level of stock but it is not clear whether this must be manually entered or is automatically updated as stock reduces.
If all these have been agreed on and populated then the only key column is Order Req but as you note the actual spreadsheet value and ENUM options may differ.
So this key column may be blank, Place Order or Seems Ok.
If it is blank the column will not show in the app as shown below
If one enters any text as shown below then it appears as you point out. This is not obvious to any user
When the row is blank even using an initial value does not work as shown below
However when on edits one gets
So from a user's point of view, one sees different things in display and edit modes and none of this is obvious to any user because of the logic that the APP uses and the logic the user wishes to apply and which requires experience in how the APP behaves to accommodate the users logic.
I am working on this and one way is to use a virtual column that overcomes this issue by suppressing the Order Req Column to overcome these non-obvious issues ๐ต
For example, if an order has been placed but stock remains below the critical value it means the order has not yet been delivered, but when the stock levels increase then it means the order has been placed and delivered and thus the display and column row value should change to Seems Ok!
Dear dbaum,
Thanks a lot for your kind approach and fast assist as usual.
It didn't work. Am i doing something wrong? Maybe it is not possible that i am trying!
on site is less then critical level but no changes on order request.
The idea is to change order request and prepare an order mail if order request is "order".
I presume you have a column called ORDER which records when an order is placed and this Column appears in your spreadsheet. So any order may only be placed once a threshold or critical level has been reached.
To make things easy for me I also added a column called CRITICAL VALUE which represents the threshold or critical value as I do not know how you determine so I just set a value of 2000. I then also created a DUMMY virtual column of type text.
Then I set ORDER to only show if the CRITICAL VALUE >=2000 and below this it will not show but the virtual column DUMMY will show instead.
If below the critical value and the user selects save no save will occur.
If equal to or above the critical value the ORDER COLUMN will show and if save is selected it will save and show the order against that row in the spreadsheet
Here is the spreadsheet with no order yet placed
Here is the app display with critical value (CV) at 2000 so it allows one to place an order as it is reached the critical value to do so
Now if one goes below the CV the order column does not show and is replaced by the DUMMY virtual column of type text which if CV is less than 2000 shows "Below Threshold" a shown below
So below the CV the dummy menu is displayed and greater and equal allows an order to be placed.
Also if a user enters save below the CV no order is placed as shown below
If the amount is greater or equal to CV then entering saves places a valid order as shown below
So creating a dummy virtual column named DUMMY with a DISPLAY NAME of ORDER and using the CV to control which column is displayed allows what you require.
You can replace CV with a formula etc but I just used a CV column for this.
Cheers
Greg Diana
Dear gregdiana1
Thanks for your well explanation and example.
I am using the same formula.
IF([On Site] <= [Critical Level], "Place Order", "Looks good")
The problem is that i have 5 items needs to follow. Would like to place order if on site of any of them is less den critical level.
| ID | Item | Min Level | On Site | Order Req | Image | Critical Level |
| 001 | Engine Straps | 100 | 55 | OPS/GBIE/OWL/ULD/big strap_image.JPG | 50 | |
| 002 | AVI Straps | 50 | 29 | OPS/GBIE/OWL/ULD/AVI straps_image.JPG | 20 | |
| 003 | ULD Tape_THRU | 3 | 4 | OPS/GBIE/OWL/ULD/thru tape_image.JPG | 2 | |
| 004 | Flight File Pouches | 100 | 60 | OPS/GBIE/OWL/ULD/cargo envelope_image.JPG | 50 | |
| 005 | Pallet Stack Corner | 100 | 90 | OPS/GBIE/OWL/ULD/pallet stack corner_image.JPG | 50 |
The problem is not able to select "Place Order" automatically even On Site is less than Critical Level. See below.
Critical level is 50 on site is 40 but place order is not selected.
Ha, ha yes the problem is far more tricky than appears. Essentially what the app is doing is checking the current stock levels against the critical stock level at which point a new order must be placed.
Things like Minimum Stock Level, Critical Stock Level are static and only set infrequently and not with any order. The client and supplier will have agreed on these and so they are mostly static.
The On-Site Stock level is the current level of stock but it is not clear whether this must be manually entered or is automatically updated as stock reduces.
If all these have been agreed on and populated then the only key column is Order Req but as you note the actual spreadsheet value and ENUM options may differ.
So this key column may be blank, Place Order or Seems Ok.
If it is blank the column will not show in the app as shown below
If one enters any text as shown below then it appears as you point out. This is not obvious to any user
When the row is blank even using an initial value does not work as shown below
However when on edits one gets
So from a user's point of view, one sees different things in display and edit modes and none of this is obvious to any user because of the logic that the APP uses and the logic the user wishes to apply and which requires experience in how the APP behaves to accommodate the users logic.
I am working on this and one way is to use a virtual column that overcomes this issue by suppressing the Order Req Column to overcome these non-obvious issues ๐ต
For example, if an order has been placed but stock remains below the critical value it means the order has not yet been delivered, but when the stock levels increase then it means the order has been placed and delivered and thus the display and column row value should change to Seems Ok!
