Formula Help

Options

I have this formula: =IF(CONTAINS("Resource", [Component Type]@row), LOWER(Curriculum@row + IF([Grade/Level]@row <> "", ".g" + [Grade/Level]@row) + IF(Module@row <> "", ".m" + Module@row) + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row) + IF(Lesson@row <> "", ".l" + Lesson@row) + "." + LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", ""), ":", ""), CHAR(22), ""), "!", ""), "?", ""), CHAR(44), ""), "á", ""), CHAR(40), ""), CHAR(41), ""), CHAR(39), ""), "'", ""), "—", ""), "...", "") + IF([Resource/Object Type]@row = "Digital Assessment", ".dig"))))

I want to add to the formula so that New Strapi Item Code for Module 1 Tips for Families populates the same output for the same translated versions of Module 1 Tips for Families. This formula will need to work for module # changing. There are 14 languages and those will remain the same per module. Right now the Strapi Item Codes for each translation show the language. I need al.g1.m1.module1tipsforfamlies to show for each translation.

Answers

  • Jgorsich
    Jgorsich ✭✭✭
    Options

    As a general thought - any time I am having issues with a formula that has multiple nested if statements, I find I can generally figure out the issue if I, starting with the lowest level statement, dedicate a column to each logical test - then I can identify where I'm getting something unexpected.

    Your current screenshot is missing quite a few columns (try right clicking on any column header and selecting "unhide all columns" or just scrolling around some - at there very least somewhere there is a column titled "Concept/Topic/Arc"), and I'm having a bit of a hard time understanding what you are trying to do - between the two issues, I can't really give more concrete advice.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!