returning only non-empty field info

[redviper]

First let me say that I am using FM Dev 6.

I have 40 fields that collect data on an individual.

Is there a method to return only data entered into any of these 40 fields and ignore the fields that are empty?

so, I have folks searching for individuals based on some search criteria. The search completes and then users are forwarded to a layout. that layout currently has all 40 fields on it. Typically, there are only 3 or 4 of the possible 40 that have any data in them.

I'd like to be able to only see the fields that have data in them without having to place all 40 fields in a layout. so, the search result would bring a name and a list of attributes associated with that record. The list of attributes would only be those fields with data in them.

is this possible in some way? I have been working with FM for many many years and this is one solution that has always baffled me.

thanks

[jamesducker]

Not easily in FM6.

If you were using FM9 or higher, you could use conditional formatting to "hide" any fields that aren't populated.

The only practicable, real-world way I can think of in FM6 would be this:

1) create a "basic" layout with the most popular fields on (say 10 of the 40)

2) create an "expanded" layout with all 40 fields on

3) make a calc field to assess whether any of the 30 fields that are only on the "expanded" layout is not empty

4) script the 'go to layout' operation that is done after the find, if the calc field is true, (ie at least one of the 30 fields is filled in) go to the "expanded" layout, otherwise go to the "basic" layout.

There would be a number of other ways to achieve similar ends, all depends how you want it to work.

Yours

James

[redviper]

I was hoping to be able to have, say, a single field that would provide a list of only the data filled fields. what you suggest is similar to what I am doing. this leaves gaps in the list where fields don't have data in them.

I have been working with an IsEmpty statement to key off the fields and perhaps try to fill a repeating field using this logic....not much success with that method so far.

could be there is no way to do this in FM6.

I wonder if I used a portal and a sort. I could maybe sort the empty fields at the bottom of the portal leaving the data filled fields on top? not sure if that would work, i'm just brainstorming.

thanks

[redviper]

ok, having some results with this:

created a calc field:

If(not IsEmpty(field1), GetField("field1"), "") & If(not IsEmpty(field2), GetField("field2"), "") & If(not IsEmpty(field3), GetField("field3"), "")

I can continue this logic for all 40 fields and it works.

it's ugly though, I need to be able to put some kind of seperator between them. It would be gravy if I could code in a hard return between them, but I don't think I can do that.

I'll have to experiment with trying to put some kind of text seperator between my results, otherwise, it's one big paragraph. Guess I could create a field with my text seperator and put a getfield function between each &, this would give me some space.

thoughts?

[jamesducker]

You could certainly have a list of only the data-filled fields, as long as you don't mind not being able to edit them.

You'd need to display a single calc field that was defined something like:

if ( not isempty(PersonName) ; "Person Name: " & PersonName & "¶" )

&

if ( not isempty(Phone) ; "Phone: " & Phone & "¶" )

&

if ( not isempty(Email) ; "Email address: " & Email & "¶" )

And so on.

I think it would be difficult to accomplish with a portal... portals list RECORDS, not FIELDS in records. The only way you could do it with a portal would be to have a script that loops through all the FIELDS, creating a new RECORD in the related file if the field was not empty. It could work, but you'd have a hurrendous mess on your hands so I wouldn't recommend it!

Hope that helps.

James

Edited October 21, 2009 by Guest
Wrapped quotes around the ¶ symbols, which you need in FM6

[redviper]

ok, too much code, I can accomplish this with only the "GetField(field)" statement, I don't need all the "if/then" stuff.

still working on the seperator.

[redviper]

ok, going to give that a try. I don't need them to edit on this layout. If they need to edit, I will pop them over to another layout that has all 40 fields on it.

I'm having a blast getting this figured out.

thanks

[Lee Smith]

Try this modified calculation posted by LaRetta the other day.

Let (

concat = Field1 & " " & Field2 & " " & Field3 ;

Substitute ( TrimAll ( concat ; 1 ; 0 ) ; " " ; ¶ )

)

[redviper]

this works but ultimately, there is another problem.

GetField("Field1") & "¶" & GetField("Field2") & "¶" & GetField("Field3") etc.....

if there's a lot of empty fields in between non-empty fields, then you get a lot of blank spaces between the lines.

I think I will try a text separator instead of a paragraph.

either way, this is working.

thanks

[redviper]

Lee,

i don't have "Let" or "concat" in my calculation choices. FM6 Dev is what I am using. These must be additional choices in a more current version.

[jamesducker]

this works but ultimately, there is another problem.

GetField("Field1") & "¶" & GetField("Field2") & "¶" & GetField("Field3") etc.....

if there's a lot of empty fields in between non-empty fields, then you get a lot of blank spaces between the lines.

I think I will try a text separator instead of a paragraph.

either way, this is working.

thanks

If you do EXACTLY as per my original example, ie:

[color:blue]if ( not isempty(PersonName) ; "Person Name: " & PersonName & "¶" )

&

if ( not isempty(Phone) ; "Phone: " & Phone & "¶" )

&

if ( not isempty(Email) ; "Email address: " & Email & "¶" )

then you will get the return-separated list with no gaps in. You'll also get the field names in there so your users can see what the fields are.

J

Edited October 21, 2009 by Guest

[Lee Smith]

: DUH

[redviper]

ok, I went in and modified the original text options in my 40 fields. I added ; " "

doing that seems to have fixed my issue but I haven't tried it with all 40 fields yet.

just to clarify, where you have "Person Name", that is a field at every instance of "Person Name"? so I substitute my fields in there and not some text I would put there?

going to give your solution a go.

thanks

[redviper]

ok James, your way is the way. I had to format it a little different though ( I don't need the field name showing - my field names are easy for calc building purposes and are not meaningful to the end user)

anyhow, here's what I did, which, I believe, is the same as what you had logically:

If(not IsEmpty(Field1), GetField("Field1") & "¶", "")

&

If(not IsEmpty(Field2), GetField("Field2") & "¶", "")

&

If(not IsEmpty(Field3), GetField("Field3") & "¶", "")

I'm off to code all 40, I am very hopefull this is the answer.

thanks folks, you really helped me a great deal.

todd

[redviper]

oh, you beautiful people!!! It works exactly how I needed it to.

I will be elevated to demi-God status thanks to this. Yeah, the folks I work for here don't have high expectations........this was a major topic of conversation at our solution design meeting.

I'll make sure you get an honorable mention James, rock on!

todd

[jamesducker]

Pleasure :-)

Glad it worked. Do post again, or email or phone, if you need anything else.

J