Mass Loop Substitute Problem...

[Genx]

Hello wonderful people

... problem relates to a script that CAN loop through a series of up to 15000 records (but usually no more than 100-200) in order to remove an id that pertains to each particular record from a global field...

the global is for a relationship so it stores a pilcrow followed by an id...followed by another pilcrow and another id and so on...

so basically the script is:

allow user abort (off)

freeze window

loop

Set Field [Ref::gFlagGlobal;

Substitute(Ref::gFlagGlobal; ¶ & Ref::id ; "" ) ]

Goto Next Record [exit after last]

end loop

now... the script works even if it does take a bit of time for larger amounts of records... HOWEVER... i am pretty much always left with a bunch of random id's at the end where the pilcrow has been substituted but the id remains i.e. my global would have the following data remaining in it

id1id2id3id4id5id6id7 etc.... * about 3 or 4...

any body have any ideas?... Currently using FM Advanced 8.0v2

p.s. no i cant just set the field = "" as not all records will always be substituted out...

cheers, genx

Edited February 17, 2006 by Guest

[Reed]

just wondering, by pilcrow, do you mean ¶? never heard that word before for a paragraph return...

There are couple of other ways you could go about this...

The FilterValues() function could remove all values except those that you wish to keep. You could populate the value list you wish to keep through a relationship with valuelistitems(), getnthrecord(), or via a script. Of course, typically, the pilcrow (using my new word already) comes after the value in a value list.

If you post your script, maybe someone could run it with the debugger turned on, so you could see where it fails.

Just throwing out ideas....

[Genx]

lol barbecue mentioned it in the shout box lol... yeh i mean the carriage return + line feed char: ¶...

debugger for that long a loop?

[Genx]

i might look into the filtervalue() function.. could i just populate another global and do it by that... if you cant be stuffed explaining it ill just look it up, but if you wanted to save me some effort pretty please

hehe, cheers anyway, genx

[Matt Klein]

It sounds like you may not be running this script in the proper context. Make sure that you are on a layout whose table occurence is "Ref".

If you are not on a layout whose table occurence is "Ref", you will be looping through the records in the current layouts table occurence and unless you have a relationship to the "Ref" table occurence from the table occurence of the current layout the "Ref::id" field will not be available. Even if you have that relationship in place, it doesn't sound like that's what you are looking for anyway.

However, Global fields do not require any relationship to the table in which it resides in in order to edit/view it. So, as you are looping through the records in the current layout and "Ref::id" is not available your substitute will still remove the return character from the global regardless of whether the "Ref::id" field is available.

That's the long winded answer. The short winded answer...make sure you are on a layout whose table occurrence is "Ref".

[Genx]

...Ref is the current TO...

[Matt Klein]

...Ref is the current TO...

I'd be interested to see what would be returned if you put the Substitute step inside the following if statement:

If[not isempty(Ref::id)]

So, it would look like:

If[not isempty(Ref::id)]

Substitute(Ref::gFlagGlobal; ¶ & Ref::id ; "" )

End If

[Søren Dyhr]

So we're talking about the opposite of FilterValues( ...what it seems like is that the id's of a found set strains a multilinekey.

Since you're on fm8adv isn't it nessersarily a looped matter:

http://www.briandunning.com/cf/193

Perhaps you should see it in action as well:

http://www.nightwing.com.au/FileMaker/demos7/BookingSystem.zip

To produce the list of ID's in the found set, did we for a long time use a dynamic valuelist and ValueListItems( but it's even more convenient to exploit this:

http://www.filemakerpros.com/GetNthRecordTAIL.zip

...and please not the differences in speed due to the stringbuffer FM apparently exploits in it's code.

--sd

[Genx]

i havent looked at the other solutions yet, but the zapvalues custom function looks like it will be perfect for the job...

cheers soren for solution & others for input

ill have a look at your last link 2 incase its even better

genx

Edited February 18, 2006 by Guest

[Genx]

... god... that getnthrecord file link... awesome.. im going to change my database to reflect certain techniques in that file... basically just the variables loop prepend thing... because the looping to global is so much less efficient... anyway, and yeh ill combine the custom function with that to solve my original problem... thank you lots and lots soren, your my hero

genx

[Genx]

rightio... back to the point, i worked out the solution to my problem... it originates from the fact that my serial id, unlike in the example i presented had no "id" at the front to delimite it..(luckily i only did this in one table within the database) so when i attempted to substitute lets say id number 63 + pilcrow.. it would also substitue all other id's with the number 63 at the end...

i.e.

7063

8063

163 and so on... so i would get the clump of numbers at the begining... 70801 ...

well better to work out the original problem late than never... ill chuck in the letters id at the begining or maybe instead extend the serial with some 0's at the begining in order to pad it a bit...

anyway, thanks for all the help, at least now i know how to use custom functions properly... and my script executes about 10 times faster which is great... so cheers for everybodies help...

sorry, for not mentioning the lack of a prefixing letter at the begining... i didnt think it was relevent... but apparently ... yeh, but thanks for everybodies time and help

genx

[NotPro]

Hi all,

I have similar problem with substitute, maybe you guru out there can help.

Part of my script looks somthing like this:

1. Set field [Post::ContactListGlobal; "]

2. Go to Related Record [From table:.........]

3. Loop

4. Set Field [Post::ContactListGlobal; Post::ContactListGlobal & Contact::ContactID & "P"]

5. Go to Record/Request/Page [Next, Exit after last]

6. End Loop

7. Go to Layout ["Post" (Post)]

8. Loop

9. Set Field[Post::ContactIDGlobal; LeftWords(Post::ContactListGlobal; 1)]

10. Set Field[Post::ContactListGlobal; Substitute (Post::ContactListGlobal; LeftWords (Post::ContactListGlobal;1) & "P";"")]

....

Of course the "P" is the carriage return symbol.

Up until step 6, ContactListGlobal is generated nicely, something like 3 5 7 38 44 46

However, when it steps through to step 10, the first couple of loops works fine, but when it comes to the double digits part, the result can be unpredictable, after 38, the next value can be 446, instead of 44 46, it is the second digit of 4 from 44 that has gone missing. Just to make it clearer, suppose the generated data is 3 5 6 46 48, when it comes to step 10 and the double digits part, ContactListGlobal can be 448. By the way, Contact::ContactID is a serialized data Auto-enter field.

Any idea how to get a clean result? Or is this a bug in FileMaker Pro 8 Adv?

Thanks a lot.

NotPro

[Søren Dyhr]

... god... that getnthrecord file link... awesome..

Now to "corrupt" you young mind further in a socratian manner, read this thread as well:

http://www.clevelandconsulting.com/support/viewtopic.php?t=637

--sd

[Søren Dyhr]

7063

8063

163 and so on... so i would get the clump of numbers at the begining... 70801 ...

Believe it or not, this is where I tend to make the relational multilinekey a repeating field instead ...although you can make it with a CF as well.

But a combination of GetNth, Get(CalculationRepetition) and Patterncount can strain a list according to the rules you make.

Try to investigate how I mean by dissecting my template here:

http://www.filemakerpros.com/Splitting.zip

Allthough I in it uses substitute instead of Patterncount(

--sd

[Genx]

lol.. your wonderful, thanks lots soren, you've managed to solve a lot of my problems for me that ive come accross over the past week trying to implement this function properly... and made my database work much smoother than it would've otherwise.. and pre-empted a fair few questions from me i would imagine

cheers again, genx

[Søren Dyhr]

9. Set Field[Post::ContactIDGlobal; LeftWords(Post::ContactListGlobal; 1)]

10. Set Field[Post::ContactListGlobal; Substitute (Post::ContactListGlobal; LeftWords (Post::ContactListGlobal;1) & "P";"")]

Right off the bat would I say that you would need to include the CRLF in line 9 as well. But you can avoid some of the clutter by using MiddleValues( which includes the tailing CRLF...

BTW is extensive use of globals wrong use local varables instead, the only live as long as the object exists.

--sd

[Genx]

the custom function is good, and ive got more than one use for it... but i think it takes longer to evaluate then the variable and loop..

~ genx

[NotPro]

Thanks for the suggestion. I tried, but it didn't work.

Apparently, a clean set of result can be obtained if I change line 9 and 10, instead of using LeftWords(), or MiddleValues(), RightWords() seems to work a lot better, except the ContactListGlobal cannot be equated to "" which is used to end the loop. Any idea why LeftWords() and MiddleValues() behave erratically?

Thanks

NotPro

[Søren Dyhr]

Any idea why LeftWords() and MiddleValues() behave erratically?

Sorry to have kept you in suspence ...Erratical is a strong word, but the issue is with the Right-, Left-, and Middle- Values( they acts if a pilcrow exists is it put on righthand side of the word, this means that if the word is plucked in the middle of a list will it be there, on the other hand if it's the last in the list might it only occure if the list ends with a pilcrow. Futher more if you in some way have a double-pilcrow somewhere in the list, are you returned the empty sting.

the Right-, Left-, and Middle- Words on the other hand truncate before any non-char, not nesserarily a pilcrow, could be a comma or a pipe | ...

But to the core in your question, why is Substitute( splitting wrongly is unfortunately not a flaw in filemaker but in your reasoning, by using it the way you do, are you sometimes getting rid of more than nessersary:

If say you wish to substitute 4¶ with "" will all values, such as 34, 44 104 loose thier last number, but what worse is, that it merges with the line below. If you're goin to use substitute for this purpose let me suggest this way where a pilcrow in both ends is substituted with a single pilcrow.

Set Field [ Post::ContactListGlobal; "¶" ] 

Commit Records/Requests [ Skip data entry validation; No dialog ] 

Go to Related Record [ From table: “Contact”; Using layout: “Contact” (Contact) ] [ Show only related records ] 

Loop 

      Set Field [ Post::ContactListGlobal; Post::ContactListGlobal & "¶" & Contact::ContactID ] 

      Go to Record/Request/Page [ Next; Exit after last ] 

End Loop 

Go to Layout [ “Post” (Post) ] 

Loop 

      Set Field [ Post::ContactIDGlobal; LeftWords (Post::ContactListGlobal;1) ] 

      Pause/Resume Script [ Duration (seconds) 1 ] 

      Set Field [ Post::ContactListGlobal; Substitute (Post::ContactListGlobal; "¶" & LeftWords (Post::ContactListGlobal;1) & "¶";"¶") ] 

      Exit Loop If [ Filter ( Post::ContactListGlobal ; "0123456789" ) = Post::ContactIDGlobal ] 

End Loop

But a more contemporary script will probably look like this - if it's going to perform the same:

Set Variable [ $i; Value:1 ] 

Set Variable [ $Times; Value:Count ( Contact::ForeignKey ) ] 

Loop 

     Set Field [ Post::ContactIDGlobal; GetNthRecord ( Contact::ContactID ; $i ) ] 

     Pause/Resume Script [ Duration (seconds): 1 ] 

     Exit Loop If [ $i = $Times ] 

     Set Variable [ $i; Value:$i+1 ] 

End Loop 

--sd

[NotPro]

Thank you SD. Yeah, "Erratical" may be is a strong word, I guess erratical applies to my not-so-logical thinking. Anyway, I will try what you have suggested, but the idea is as follow:

A script step is used to gather list of contacts using Contact ID, therefore, the compiled list of ID could look something like 3 5 8 34 44 46 51 ..., however, after a couple of substitute, the string could look like: 344 46 51..., and subsequent substitute it may look 446 51..., and by the time the entire string has been substituted, the string can not be equated to blank. Anyway, I will try what you have suggested.

Thanks a lot.

NotPro

[Søren Dyhr]

he compiled list of ID could look something like 3 5 8 34 44 46 51 ..., however, after a couple of substitute, the string could look like: 344 46 51.

Same issue, Substitute( replaces all occurences not just the first one. But if you wish to have a string like that, could you do it with one single line of script. Make a dynamic valuelist of recordID's This will via ValueListItems(Get(Filename);"theList")

A single Substitute( that switches all the pilcrows into space is what it takes, completly sans looping at all. If you were on FM8Adv, would you instead of ValueListItems( use a variation over this custom function Ray uses in this template:

http://www.nightwing.com.au/FileMaker/demos8/demo804.html

It goes like this: GetValueSet(Field;StartValue)

GetNthRecord ( Field; StartValue ) & 

If(IsValid(GetNthRecord ( Field; StartValue + 1));

"¶" & GetValueSet(Field; StartValue + 1))

...the variation it takes, is to change the pilcrow in the CF to

--sd