Forum Discussion

LandonSmallwood's avatar
LandonSmallwood
Qrew Member
24 days ago
Solved

Inspection Sheet Possibilities

I am attempting to build an inspection sheet that will state if the dimension entered is within the specified tolerance.  I have attached a screenshot of how I would like it to function, however after a week of reading the community pages. I have had little success in making it operational.

Current Setup(see attached): Column #1 (Data Entry-Multi Choice or Numeric).  Column #2 (output based on Column #1, OK/Green, Out of Tolerance/Red) Column#3 (Section view, visual reference only)

I have had varied success with Column #2 as Text-Formula or Rich Text Formula using If statements with Greater Than, Less Than, and Equal to statements. ie. it does something but not the desired outcome.

Questions:  Is my idea possible?

If so, Is Numeric/Multi Choice the correct choice for Column #1? Am I missing a step where I need to change the numeric into text and then apply the formula?

Any help or ideas on how to accomplish this would be greatly appreciated.

Regards,

Landon

formulas
  • MarkShnier__You's avatar
    MarkShnier__You
    15 days ago

    Admittedly they don't make it easy.

    try this

    var text MyNumber = ToText([Measurement]);

    var text NumberWithDecimalInTextFormat = $MyNumber

    & If(not Contains($MyNumber,"."),"."); // deal with an exact integer measurement

    var text IntegerPart = Left($NumberWithDecimalInTextFormat,".");

    var text DecimalPart = PadRight(Right($NumberWithDecimalInTextFormat,"."),3,"0");

    var text FinalResult = List(".", $IntegerPart, $DecimalPart);

     

    If([Ø 0.328]<=0.333 and [Ø 0.328]>=0.323,"<span style='font-size: 15pt; text-align: left; color: green'>" & $FinalResult & "</span>",

    [Ø 0.328]<0.323,"<span style='font-size: 15pt; text-align: left; color: red'>" & & $FinalResult & "</span>",

    [Ø 0.328]>0.333,"<span style='font-size: 15pt; text-align: left; color: red'>" & & $FinalResult & "</span>")

7 Replies

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    24 days ago

    I am trying to understand your question. Is your question how to display a some text field with a coloured background depending on the selection in another field?

  • Denin's avatar
    Denin
    Qrew Captain
    23 days ago

    If I understand correctly I think you need multiple fields. You need numeric field for user input. You need formula fields that will calculate the input value and see if it's in range. Then separately, you want formula fields that take the calculated value from the formula field and do a simple condition to check if it's valid or not and output the message.

  • LandonSmallwood's avatar
    LandonSmallwood
    Qrew Member
    17 days ago

    In follow up I was able to get my idea functional using Numeric Entry Fields and Rich Text Formula of:

    If([Ø 0.328]<=0.333 and [Ø 0.328]>=0.323,"<span style='font-size: 15pt; text-align: left; color: green'>" & ToText([Ø 0.328]) & "</span>",

    [Ø 0.328]<0.323,"<span style='font-size: 15pt; text-align: left; color: red'>" & ToText([Ø 0.328]) & "</span>",

    [Ø 0.328]>0.333,"<span style='font-size: 15pt; text-align: left; color: red'>" & ToText([Ø 0.328]) & "</span>")

    Solved multiple choice coloring with the formula below

    Case([45° x .010 (Visual)],

    "OK", "<span style='font-size: 15pt; text-align: left; color: green'>" & ToText([45° x .010 (Visual)]) & "</span>",

    "Out of Tolerance","<span style='font-size: 15pt; text-align: left; color: red'>" & ToText([45° x .010 (Visual)]) & "</span>")

    However I do have 1 more issues to solve.

    - If the last digit of the dimension entered is a 0 (zero) then it is removed when displayed through the formula (see bottom entry on attached, instead of displaying .330 it changes it to .33).  Is there a modification to the formula above to insure it displays exactly the numeric that is entered?

    • Roy-Wanyoike's avatar
      Roy-Wanyoike
      Qrew Assistant Captain
      15 days ago

      The problem is ToText() removes trailing zeros. You need to use Format () instead to force decimal places.

      var text MyMeasurement = ToText([Measurement]);
var text MyDecimalMeasurement = $MyMeasurement
& If(not Contains($MyMeasurement,"."),"."); 
var text IntegerPart = Left($MyDecimalMeasurement,".");
var text DecimalPart = PadRight(Right($MyDecimalMeasurement,"."),3,"0");
var text FinalMeasurement = List(".", $IntegerPart, $DecimalPart);
If([Ø 0.328]<=0.333 and [Ø 0.328]>=0.323,"<span style='font-size: 15pt; text-align: left; color: green'>" & $FinalMeasurement & "</span>",
[Ø 0.328]<0.323,"<span style='font-size: 15pt; text-align: left; color: red'>" & & $FinalMeasurement & "</span>",
[Ø 0.328]>0.333,"<span style='font-size: 15pt; text-align: left; color: red'>" & & $FinalMeasurement & "</span>")