Why won't this recurse

[David Jondreau]

I'm designing a custom function and I'm most of the way there, but I'm stuck on one aspect. The CF is designed to concatenate all standard fields into an indexable calculated field, which can then be searched.

Concatenate(TableName; Start) =

//This function will create a value delimited list of all the stored values in all the fields of a table. Start should always be 0. TableName should be in quotes.

Let([

File = Get(FileName);

FieldList = FieldNames(File;TableName);

NumberFields = ValueCount(FieldList);

Counter = Start+1;

FieldName = GetValue(FieldList;Counter);

FullFieldName = TableName & "::" & FieldName;

Storage = LeftWords(FieldType(File;FullFieldName);1);

Value = Case(Storage = "Standard"; Evaluate(FieldName) & "¶";"")];

Case(counter ≤ NumberFields; Concatenate(Quote(TableName); Counter);

Value)

)

My Let statement is fine. If I place Value alone without the Case statement, I always get the correct Value depending on the Counter. But when I try to recurse the function, I just get a null value. I have tried the recursion with and without the Quote() function. Any ideas??

DJ

[Genx]

Well for one thing you aren't passing any concatenated list of values over to the function each time you call it are you? You're just passing the counter -- you need to pass a result value as well.

i.e.

Let[...

Result = If( not IsEmpty(Result); Result & "¶" ) & Value ];

Case( counter <= NumberFields; Concatenate( TableName ; Counter ; Result ) ; Result )

[David Jondreau]

Right. Completely missed that I'm not passing the value. How does that happen? So now I've added it:

Let([...];

Case(

counter ≤ NumberFields; Value & Concatenate(TableName; Counter);Value)

)

And taken out the Quote function and it works!

Thanks!

[Genx]

Hi David,

Could you post the final cf for anyone who's interested please?

Cheers.

[David Jondreau]

Definitely. I've just started going public with my attempts at custom functions so I'm not sure *when* it's final. Most of my work isn't 'polished'. But I know it's not done yet...The calc won't update when a value changes.

I may be barking up the wrong tree...Does a Stored Calc not update unless the fields of the calc are named explicitly?

DJ

[Genx]

I may be barking up the wrong tree...Does a Stored Calc not update unless the fields of the calc are named explicitly? ...

No stored calcs will only update when fields referenced using their real names are changed (i.e. embedding constructed names into an evaluate() statement won't work)... you can however sort of force it to refresh when a particular field is changed (or any given list of fields) by using the second half of an evaluate statement -- Evaluate( Expression ; trigger fields )

But on that note, why not just have it unstored?

[David Jondreau]

Hmm. Another hurdle to overcome.

My goal is to build a custom function that can be dropped into any solution that will create an index field that can then be searched in a Google-like manner. I.e. One text box, ALL the results.

I don't know if there is a simpler way to do this, but lately I've been really enjoying the learning experience of making custom functions.

[Genx]

I thought that might have been what you were trying...

Well, here's the thing... google has little spiders that go around an index it's pages.

So either you put in custom navigation and simply execute a mini script each time the user goes to the next record (or use an event trigger script)... Or you make your own little spider let it crawl around your db every hour or so on an individual client

[David Jondreau]

I understand how a search engine works. One of the reasons Google employs spiders is because it has no way of knowing when content is added or changed. Filemaker has that ability within tables (thought not between them).

Custom naviagation and FM spiders are interesting ideas for sure, but as I said,

...my goal is to build a custom function that can be dropped into any solution that will create an index field that can then be searched in a Google-like manner.

What do you envision an FM spider looks like?

[Genx]

I wasn't suggesting you didn't understand how one worked -- just saves the time in case you or someone reading this later doesn't.

Anyway, well the FM spider... hmmm.

I'd personally go with the gradual update method, but i'd imagine something like this (note this script is slightly paraphrased)

Set Variable[$i ; 1]



#Go Through Each and Every Layout

Loop



If[$i = 0]

Pause/Resume Script[Duration: 30*60]

Set Variable[$i ; 1]

End If



Go To Layout[by Number... ; $i]

Go To Record[First]



#Go Through Each and Every Record

Loop

Go To Object["Index" ]

Insert Calculated Text[ Concatenate() ]

Go To Record[ Next ; Exit After Last ]

End Loop



Set Variable[$i ; If( $i < ValueCount(LayoutNames(Get(FileName))) ; $i+1 ; 0)]

End Loop 

Edited April 6, 2007 by Guest