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.
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".
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.
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.
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.
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.
That worked -- Thanks! Actually, both solutions work. Thanks for the feed back.
Hey guys, just wondered if you could help a bit further with my calcuation;
My 6 fields are;
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?
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:
- It's only visible in preview mode and when you print/save as PDF...
- 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.
- Objects in headers and footers will not slide.
- Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
- Consistent side borders are difficult to achieve with sliding fields.