9 Replies Latest reply on Jul 19, 2011 2:07 PM by philmodjunk

    CalcField Help

    MatthewMackay

      Title

      CalcField Help

      Post

      I'm trying to work on a calculation field at the moment but think I'm going the completely wrong way about it.

      I have 6 text fields that I'm trying to pull together to make 1 sentence on the report layout. I've started writing the calculation using Case not/IsEmpty but have realised there are hundreds of different possible outcomes and surely this isn't the most efficient way to use a calcfield.

      Any help?

        • 1. Re: CalcField Help
          bumper

          Assuming that your six fields are in the same record AND are either empty or have data AND if they have data you want to use in your sentence AND the order of the data from the six fields stays the same regardless of the contents. For simplicity just string together six

          If(IsEmpty ( Field1 ); "", Field1 & " ") & If(IsEmpty ( Field2 ); "", Field2 & " ") & If(IsEmpty ( Field3 ); "", Field3) ...

          The first double quotes have nothing between them and return a null or nothing, the second has a space which will automatically put a space between the data from fields when they have data. No space needed for the last If statement. You may have to adjust this for your data punctuation needs.

          • 2. Re: CalcField Help
            Bilic

            That calculation works perfect when creating new records.  However, how do you get a field to combine 2 fields that already have data in them.  For example, I have already entered "Blue" in one field and "Sky" in another and I'm wanting the 3rd field (which is a new field) to display "Blue Sky".

            • 3. Re: CalcField Help
              bumper

              If you are creating a new calculation field (or redefining an existing one) it will automatically perform the calculation when you go back to Browse Mode.

              If it isn't give me a little more information.

              • 4. Re: CalcField Help
                Bilic

                If you create a new database and have 3 fields: A, B and C.  Create 4 records and put words in field A and B.  Field C is added later (after the 4 records have been inputted) and it combines the data from A and B.  My formula in field C reads:

                If(IsEmpty ( sequence ); ""; sequence & " ") & If(IsEmpty ( element ); ""; element & " ")

                If you add this formula after entering the 4 records then the formula won't work on those 4 records already entered.  Field C is blank on those records.  However, when you create a new record, field C will calculate and fill in.

                • 5. Re: CalcField Help
                  philmodjunk

                  There's a difference between using a field of type calculation--which updates automatically for existing records and a text field with an auto-entered calculation that will not update for existing records. If you still want the auto-entered calculation, you have to use other means such as a replace fiedl contents operation to update your existing records.

                  Also, if you just want to string together several fields that are either blank or contain data, you might want to use this expression:

                  Substitute ( List ( field1 ; field 2 ; field 3 ; field 4 ) ; ¶ ; " " )

                  The list function will omit out the empty fields for you so this can be just a little bit simpler expression for this.

                  • 6. Re: CalcField Help
                    bumper

                    My bad, I didn't know you were using an AutoEnter text field.

                    You can force the issue by doing a

                    Replace Field Contents... > Replace with calculated result,

                    on one of the text fields and enter the name of that field in the calculation box. This replaces the contents of the field with what is already in the field, but the AutoEnter thinks it is new data and will populate C.

                    MAKE Backups of your files before trying any Replace.

                    • 7. Re: CalcField Help
                      Bilic

                      That worked -- Thanks!  Actually, both solutions work.  Thanks for the feed back.

                      :)

                      • 8. Re: CalcField Help
                        MatthewMackay

                        Hey guys, just wondered if you could help a bit further with my calcuation;

                        My 6 fields are;

                        Colour

                        Material

                        Description

                        Fittings

                        Brand

                        Serial

                        Each of these needs to be on a new line apart from colour and material. If the user has entered colour and material I need the report to show colour and material with a space between and then any further fields below eachother. Or if the user enters just colour or just material, show either then the rest of the fields on seperate lines. If the user doesn't enter colour or material, I don't want a return before the other fields begin.

                        Quite a complicated explanaton, any thoughts?

                        • 9. Re: CalcField Help
                          philmodjunk

                          List ( Substitute ( List ( colour ; material ) ; ¶ ; " " ) ; Description ; Fittings ; Brand ; Serial )

                          For printing/PDF/Preview purposes only, you could also have a calculation field set up as just: Substitute ( List ( colour ; material ) ; ¶ ; " " ), then put this calculation field on the layout, with the Description, Fittings, Brand, Serial fields placed below it. Then set all these fields to "slide up", "resize enclosing part".

                          Key facts about sliding layout objects:

                          1. It's only visible in preview mode and when you print/save as PDF...
                          2. All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
                          3. Objects in headers and footers will not slide.
                          4. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
                          5. Consistent side borders are difficult to achieve with sliding fields.