0

Formulation Issue

I have a table called 'Trial Summary' with a field 'T#' that I need to reference in a formula, but I only want to reference the currently selected 'T#'.

Here's what I have so far:

let p := text('Apps Project'.'Project Variables');
let x := if contains(p, "Cure Temp") then
'Apps Project'.'Trial Summary'.'Ink Cure Temps'.'Cure F' + " for"
else
'Apps Project'.'Ink Cure Temps'.'Cure F' + " for "
end;
let y := if contains(p, "Cure Duration") then
'Apps Project'.'Trial Summary'.'Ink Cure Duration'.Seconds + " or " + 'Apps Project'.'Trial Summary'.'Ink Cure Duration'.MinSeconds
else
'Apps Project'.'Ink Cure Duration'.Seconds + " or " + 'Apps Project'.'Ink Cure Duration'.MinSeconds
end;
string(x + y)

And the resulting string is pulling the 'Ink Cure Duration' data from two 'T#'s instead of just the one being viewed:

280*F for 60s90s or 01:0001:30

I want it to display as:

280*F for 60s or 01:00

I know I need to reference the current 'T#' in the formula, but I don't know the proper syntax to do so.

Also, is there a way to get the degree symbol to appear in a field? Right now I'm using "*" as a placeholder.

Any suggestions will be greatly appreciated.

13 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

    Are you sure that there is only one record being returned by 'Apps Project'.'Project Variables'? You could put that in a formula field by itself to see what it is returning. It looks like you are getting an array of values, 2 in your example. If that's the case, you should be able to use...

     

    let p := item('Apps Project'.'Project Variables', 0)

     

    to get the first item. Per a Google search, you can use Option+Shift+8 or Option+K to insert two different types of degree symbols. You can also go to the Edit menu and select Emoji & Symbols.

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    Thanks for the degree symbol shortcut.

    As for the rest, I think I'm not communicating the idea clearly. I think this code is closer to what I'm looking for:

    let myID := this;
    let myT := myID.'T#';
    let p := text('Apps Project'.'Project Variables');
    let x := if contains(p, "Cure Temp") then
    'Apps Project'.'Trial Summary'.'Ink Cure Temps'.'Cure F' + " for"
    else
    'Apps Project'.'Ink Cure Temps'.'Cure F' + " for "
    end;
    let y := if contains(p, "Cure Duration") then
    'Apps Project'.'Trial Summary'.'Ink Cure Duration'.Seconds + " or " + 'Apps Project'.'Trial Summary'.'Ink Cure Duration'.MinSeconds
    else
    'Apps Project'.'Ink Cure Duration'.Seconds + " or " + 'Apps Project'.'Ink Cure Duration'.MinSeconds
    end;
    string(x + y)

    myT returns the current records 'T#', but I don't know the syntax to get the value of the field 'Apps Project'.'Trial Summary'.'Ink Cure Duration'.Seconds for myT. Currently it's returning 'Apps Project'.'Trial Summary'.'Ink Cure Duration'.Seconds value for all 'T#' in the 'Trial Summary'.

    For further clarification 'Project Variables' is a multiple choice field that allows the user to select variables to apply to specific trials as opposed to all trials. In the example from the previous post I have only selected 'Ink Cure Duration' as a trial specified variable, which is why it's returning multple results (60s and 90s or 01:00 and 01:30), whereas 'Ink Cure Temp' is not a variable per trial and is being defined by 'Apps Project'.'Ink Cure Temps'.'Cure F'.

    Hopefully that helps clarify. Let me know if you come up with something. As far as I can tell, it should be a simple solution, but syntax ignorance is getting in the way.

    • Sean
    • 5 yrs ago
    • Reported - view

    Are the following separate tables?...

     

    'Apps Project'

    'Trial Summary'

    'Ink Cure Duration'

     

    Which table is myID referring to? Which table is myID.'T#' being compared to?

     

    If you want to get the values from a Multiple Choice field you use chosen() or numbers().

    • Mconneen
    • 5 yrs ago
    • Reported - view

    Yeah.. that "numbers" was a hidden gem.. :) 

    • Sean
    • 5 yrs ago
    • Reported - view

    😆

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    'Apps Project' is a table with 'Trial Summary' as a subtable.

     

    'ink Cure Duration' is being referenced by both tables, but is only available to populate in 'Trial Summary' if "Cure Duration" is selected from the 'Apps Project'.'Project Variables' multiple choice field.

     

    The 'T#' being referenced is technically 'Apps Project'.'Trial Summary'.'T#'

     

    So my code is essentially trying to look at whether or not 'Ink Cure Duration' data is being applied at the 'Apps Project' level or the 'Trial Summary' level and then stringing that data out in the formula field. The code succeeds at differentiating where the data is coming from; however, it is currently stringing the 'Ink Cure Duration' data from all trials ('T#') within the 'Trial Summary'. I need it to only produce the data for the trial being viewed.

     

    I will have to look into chosen() or numbers() for referencing multiple choice fields. Correct me if I'm wrong, but I don't think it would have an effect on what I'm trying to achieve in this circumstance other than a change in syntax.

     

    Let me know if this helps.

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    Forgot to point out that 'Ink Cure Duration' is also a table. Sorry about that.

    • Sean
    • 5 yrs ago
    • Reported - view

    Don't worry about chosen() or numbers(), what you are doing with contains() works fine.

     

    I'm going to have to look at the rest again tomorrow. It's been a long day for me.

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    Thanks, Sean. I appreciate it.

    • Sean
    • 5 yrs ago
    • Reported - view

    William, the only way I was able to duplicate your error was by linking more than one 'Ink Cure Duration' record to one 'Trial Summary' record. Will you normally only have one 'Ink Cure Duration' record per 'Trial Summary' record or 'T#'?

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    It will often be necessary.

    The 'Trial Summary' table contains multiple 'T#'s (or trials). The 'Ink Cure Duration' may vary from 'T#' to 'T#' (or trial to trial) within the 'Trial Summary'.

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    Well, I figured it out. Turns out I was being too specific in my callouts for the tables. The addition of 'Apps Project'.'Trial Summary' to 'Ink Cure Duration' was what was grabbing the data from all the 'T#'s. Once removed, it behaved as intended.

     

    let p := text('Apps Project'.'Project Variables');
    let x := if contains(p, "Cure Temp") then
    'Ink Cure Temps'.'Cure F' + " for "
    else
    'Apps Project'.'Ink Cure Temps'.'Cure F' + " for "
    end;
    let y := if contains(p, "Cure Duration") then
    'Ink Cure Duration'.Seconds + " or " + 'Ink Cure Duration'.MinSeconds
    else
    'Apps Project'.'Ink Cure Duration'.Seconds + " or " + 'Apps Project'.'Ink Cure Duration'.MinSeconds
    end;
    string(x + y)

    Thanks for all the help and sorry for sending you on a wild goose chase. Another syntax lesson learned for me!

    • Sean
    • 5 yrs ago
    • Reported - view

    No worries. Glad you got it working.

Content aside

  • 5 yrs agoLast active
  • 13Replies
  • 2688Views