Determining if current field is empty

[Slater]

Hi Everyone,

I've been trying for ages and ages!

The complicated bit is that i don't know what field it is that i'm trying to find out about ... i've got an array, and I'm wanting to go from field to field checking if it's empty.

Cos the array is kinda large (20-30) I've set the script up as a loop, and got it going from field to field (using goto next field) checking if the field is empty (my problem!) - if it's not, the contents get copied out, and the field name gets copied into another field which all then get put into another data base. It's all working fine except the determination of whether the field is empty.

I've been trying to use Status(currentFieldName) but that just returns the field name which, even if i put that into IsEmpty(Status(CurrentFieldName) ... it always comes up that it's not empty (cos the field is always called something) ... doh! And there is no Status(currentFieldContent) ... it's driving me mad!@!

I'm sure i'm just missing something, as it's probably a thing that lots of people use / need, but i just can't seem to crack it.

Thanks very much for any advice you can give,

slater

[Robert Kidd]

Are you talking about different fields on the same layout or a repeating field ?

IsEmpty(fieldname) is probably what you need but with multiple fields it's a little tricky.

[Ugo DI LUCA]

If you have FM 5.5 or later, then not Is Empty(GetField(Status(CurrentFieldName)))

Try a script

If[not is Empty(GetField(Status(CurrentFieldName)))]

SetField[g_tempFieldName, Status(CurrentFieldName)]

SetField[g_newField, g_tempFieldName]

Now, it's hard to understand how you're moving records to the other db, but this might give you a start.

Storing the fieldName in g_tempFielName helps going back to that field for the loop when you've exited it.

Oh, and what about the array ? Is it a repeating field or what ?

[Ugo DI LUCA]

Oh let see if I understood...

GoToField[YourFirstField]

Loop

If[not is Empty(GetField(Status(CurrentFieldName)))]

SetField[g_tempFieldName, Status(CurrentFieldName)]

SetField[g_newField, g_newField&"

[Slater]

Hey Ugo!

I'm on FM5, so there doesn't seem to be a GetField ... what i've got is something like this:

Loop

Goto next field @BEFORE THE LOOP, POSITION SET TO START FIELD

If[isEmpty(status(currentFieldName))] @WHICH IS THE BIT THAT DOESN'T WORK

copy [select] @THE PRESENT FIELD

Set Field[Field_name, status(CurrentFieldName)]

Set Field[Count, Count+1]

Goto Related Record [Plan] @THE DB THAT IS EXPORTED TO

Perform Script[subscritp, external: "da da" @ENTERS PASTEBOARD INTO FIELD

Perform Script[subscritp, external: "da da" @ENTERS FIELD_NAME INTO A DIFFERENT FIELD

end if

end loop

Thanks so much for the help, sounds like GetField is what i want. They're different fields, not repeated fields, set out in an array, ie field xyz1, xyz2, abc1, abc2 etc, so the goto next field just jumps the current field to the next one, without me knowing what it's called because it's looking out from the loop, which is the same regardless of which field is the current one.

Will i have to upgrade to be able to do what i want, do you think?

Thanks again, slater

[Robert Kidd]

Put a counter on your loop.

As you go to each field copy the data and paste it into a test field

Run your IsEmpty query on this test field every time.

If true you can get the FieldName using a choose function ie choose(loopcounter,fielda,fieldb...).

You will have to set another field with the current field No ( loop count )

and then skip through the previously checked fields again as after every paste to the test field you will loose your "position" on the layout.

Hope this makes sense.

[Slater]

Ha Ha!

Cracked it, well, almost - so thanks for your help! I did it as suggested, with the script pasting and testing and, if the field is not empty, pasting to the destination; then going through a number of next fields until it gets to what was the next one, which it copies and tests ... i've included a screen shot of the script as an attachment.

THE ONLY PROBLEM is that when the script goes back to the DB to paste the info into, it has reverted back to the first record that has a valid relation ship. It's a one to many relationship - so the "one" goes back to the "many" and picks the top of the pile, which might not be the one that it came from when the script started.

So sorry to be full of questions, but is there a way to keep the destination "many" from going back to the top of the pile, ie keeping it at the record that it was at when the script started?

In the script that i've included as a simple text picture the problem is at the line that says

goto related record[M_Plan] @JUST BEFORE THE TWO EXTERNAL SCRIPTS

So when the script starts, it is with M_Plan recordX being current, but then when the script goes back there, it has reverted to recordA - the top one.

Doh! and i thought i had it sorted! (My girlfriend wasn't that impressed!)

Thanks for any help, and especially to the guys that have already helped,

slater

Picture 2.txt

[Ugo DI LUCA]

Hi,

May be if you explained to us what is your exact intent with such a script, we may come up with another solution, as it seems currently a real pain, at least on what I understood so far.

Nevertheless, and in the meanwhile, I 'd like to make sure for first that you are using the SetField[ ] script step rather than the Copy/Paste procedure. If not, you should.

Now, if I correctly followed (your attachment didn't came through), the script you posted is an external script that you call from the Related File, using your Foreign Key as a match for a GoToRelatedRecords to the Main File, matching the MainID.

---> Relationship#1 --- ForeignID::MainID

You have problem at end script, as you are targetting the firt related record in the Related (Many) file, by using the reverted relationship :

---> Relationship#2 --- MainID::ForeignID

If so, what you could need is to :

1. store this "Related Key" (assuming you have an ID for each record in related) when the script is launched in another global "temporary" field. Therefore, the script would start :

'Related File Script'

SetField[g_storeID, RelatedID]

GoToRelatedRecords[Relationship#1 -show only]

PerformExternal Script [The buggy one]

2. At the very beginning of the Main file script (externalscript here), set another global to the g_storeID value, using the reverted relationship

SetField[g_storeIDRelated,::Relationship#2:g_storeID]

3. Establish a relationship from this latest global back to the Related file ID.

Relationship#3: Main:g_storeIDRelated::Related:RelatedID

4. Then, you'd go back to the related file, at end script, exactly to that record you had left by using a :

GoToRelatedRecords[Relationship#3- show Only]

NOW,

Did you know the IsEmpty() also applies for relationship ? I think involving it would help simplify your script in related

SetField[Field1, Case(notIsEmpty(Relationship#1:Field1, Relationship#1:Field1,"")

SetField[Field2, Case(notIsEmpty(Relationship#1:Field2, Relationship#1:Field2,"")

.....

Which now makes me think that you could even simply get these values from a lookup.

Hope this help and answers your problem

[Slater]

Thanks Ugo,

I've got something on right now, but i'll give it a whirl and re-send the script as a screen shot jpeg on my return

All the best

slater

[Slater]

Fantastic - it's all working now!! So thankyou ever so much ... was really stupid of me to not think about making another relationship based on the unique id field for the DB i wanted to come back to ...

anyway, here's the script based in the "array" database as a jpeg.

Thanks again,

slater