3 Replies Latest reply on Feb 1, 2015 1:27 PM by philmodjunk

    Format within a calculated field and Text Box with Script

    MichaelShea_1

      Title

      Format within a calculated field and Text Box with Script

      Post

      Forum Members,,, I am almost done with my project, I have to items I am currently trying to resolve:

      1) How to format a number (result of calculation) within a text statement within a calculated field. Example:

      "After expiration penalty of $" & .02*Original Bill &" and daily interest of $"&.01*(1.02*Original Bill)&"."

      How can I format the number results " & .02*Original Bill &" to appear $#,###.00


      2) I have created an output table that will generate information based on a single Client ID. How can I create a script, that will bring up a text box that will ask me to input the desired Client ID, and then the calculated fields can refer to that Client ID to run their related Lookups?

      Much appreciated!!!
       

        • 1. Re: Format within a calculated field and Text Box with Script
          philmodjunk

          1) Why do you need to combine the text in this calculation field in this manner? There may be a good reason, but if not, you could put the following layout text on your layout:

          "After expiration penalty of <<Original Bill_02>> and daily interest of <<Original Bill_102>>."

          Where the two fields are calculation fields that multiply values as shown in your original example. You can select this entire block of layout text and specify currency formatting in the inspector (data tab) to get the dollar signs, commas and two decimal place formatting.

          2) Don't think you need "lookups" here at at all. A "lookup" copies data from one table into another and stores it. But what you describe is a matter of setting up a relationship based on a field used for selecting a client ID.

          I'll describe the "beginner level" method for doing this here and then share some download links to a pair of files that offer some more user friendly options better suited for when you have a long list of clients.

          Define this relationship:

          LayoutTable::SelectedClientID = RelatedTable::_fkClientID

          Format SelectedClientID with a drop down list or pop up menu that uses a "use values from field" value list to list both ClientID's and ClientNames (field1 and Field2). This allows you to select a client by name while the value list enters the ID. This then establishes a link to RelatedTable and you can use this relationship in the following ways:

          If there is just one record in RelatedTable with each given ClientID, you can put fields from Related Table directly on your layout. If there are multiple records, you can use a portal to display them.

          IF there is just one client record in RelatedTable for each ID, calculations can now refer directly to the fields of the record in the relatedTable.

          Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a use values from field value list)
          Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Format within a calculated field and Text Box with Script
            MichaelShea_1

            I much appreciate all the assistance.  I was able to solve my formatting issue with the following solution found on the web.

             

            All the best.

            If ( IsEmpty ( Price ) ; "" ; "$" & If ( Price > = 1000 ; Int ( Price / 1000 ) & "," ; "") & Right ( Int ( Price ) ; 3 ) & Left ( Price - Int ( Price ) + .001 ; 3 ) ) & " US"

            • 3. Re: Format within a calculated field and Text Box with Script
              philmodjunk

              Yep, but it's a pretty complex way of producing the needed formatting. Sometimes such an approach is necessary as the merge text approach doesn't meet every need, but if you can use merge text, that's a far simpler approach.