7 Replies Latest reply on Feb 17, 2014 6:24 AM by NickLawrence

    Enter Text in to Calculated Field

    NickLawrence

      Title

      Enter Text in to Calculated Field

      Post

           Hi all,


           I have 4 fields:

           Fees Due  -  % Due  - Role  - Total

           The amount is entered in to the Fees due, the % due is a look up and dependent on what is selected from a drop down menu in the role field and the total is a calculation (Fees Due * Role %)


           Fees Due = £1,00.00

           Role = Role 1 (% is 10%)

           % Due = 10%

           Total = (£1,000 * 10%) £100.00


           Problem is I have 2 roles that regardless of what the fee is they have a fixed rate so I need to know how to get the % Due field to  display 'Fixed' instead of the % amount and the calculation field to show the fixed amount (e.g. £200) rather than being empty. Alternatively I need to make the calculation field modifiable, so I can enter the £200 manually.


           Is there a way to do any or all of the above?

           Many thanks

           Nick

        • 1. Re: Enter Text in to Calculated Field
          philmodjunk

               If your % Due field looks up 100%, then it would appear that your Fees Due field could look up the flat fee ( which is currently £200)

               Then you just need to display the text "fixed" at the correct time.

               Option 1: Type "Fixed" on your layout as layout text. Use conditional formatting to make "Fixed" invisible except when the role is one of the two values for fixed dues. Do the same for the % Due field using conditional formatting to make it invisible when "Fixed" is visible. To make the text invisible set the font size to a very large font size (for layout text or field in a single row and font size 10 or 12, changing the font size to 120+ usually makes the text/data invisible.

               Option 2: define an additional calculation field that returns either "FIxed" or the contents of % Due depending on the value of Role (use a case function) and put it on your layout in place of the % Due field.

          • 2. Re: Enter Text in to Calculated Field
            NickLawrence

                 Thanks Phil,

                  

                 I think the conditional suggestion may solve the first part, need to try it out will also look at the calculation aspect too.

                  

                 Many thanks for your help

                  

                 Kind regards

                  

                 Nick

            • 3. Re: Enter Text in to Calculated Field
              NickLawrence

                   Hi Phil,

                    

                   Done what you suggested and worked a treat, one query I'm using repetitions and if I change things in the other repetitions it only changes the first one, is there a way of specifying linking to the relevant line?

                    

                   Many thanks

                    

                   Nick

              • 4. Re: Enter Text in to Calculated Field
                philmodjunk

                     To repeat an answer posted to another thread, that is probably by you to ask this same question. That's a built in limitation of repeating fields. The solution is not to use them. Use a table of related records, one record in place of each repetition to store the same data. Then you will not have an issue with looking up data from a related table as now you are not using a repeating field.

                     Note that repeating fields were once the only way of entering multiple items associated with the same record. But the release of FileMaker 3 made using related tables possible and the use of repeating fields has been the less than best way to record such data ever since that version release.

                • 5. Re: Enter Text in to Calculated Field
                  NickLawrence

                       Actually one was a look up question and one was a calculation question so if they are the same then I stand corrected.

                  • 6. Re: Enter Text in to Calculated Field
                    philmodjunk

                         Yet since you need to move to using a related table of records, this question is no longer an issue.

                    • 7. Re: Enter Text in to Calculated Field
                      NickLawrence

                           Will this work in a portal? I still need it to display 'Fixed' when a certain role is selected. Basically I'm trying to create a purchase order template.

                            

                           Many thanks

                            

                           N