Dictionary/Hash function from any table?

[xochi]

I'd like to have a generic glossary/dictionary/hash function that takes a keyword and returns the corresponding value. I'm storing these in a simple table that has two fields: Keyword, Value.

I've got it working just fine with Custom Functions and GetNthRecord() recursive function.

However, it seems that this will only work when the context is in a table that is related to the keyword/value table. I'd like to be able to use this anywhere in my solution, on any layout, without worrying about adding new TOs.

In other words, I'd like to have a relationship that functions as a global cartesian join between every table and this one particular data table.

Anyone solved this problem?

[Razumovsky]

I have done something similar without recursion or a CF by creating 2 value lists, each sorted by the same criteria so that Value 6 of one would be the definition of value 6 from the other. Then it is just a matter of using position to find the correct value.

This method requires that each word and each definition be unique and a single value.

Does that help?

[xochi]

Thanks for the suggestion. I really want to keep the data in a table, so not sure value lists would work for me.

After looking at it some more, I realize that I only have to add about 3 TOs to my graph. Then, I can use a Get function to determine which relationship to use in the custom function, like this:

GetToolTip(keyword) =

// gets the matching ToolTip based on the keyword, using the

// GetToolTipRecursive helper function





Let(

[

 TO = Get(LayoutTableName);



 N = Case(

    TO="HelpDesk" ; Count(ToolTip X HelpDesk::Serial) ; 

    TO="Reports" ; Count(ToolTip X Reports::Serial) ; 

    Count(ToolTip X Worker::Serial) 

  ) 

] ;  

 

GetToolTipRecursive(KeyWord; N)



)





___________________________________________





GetToolTipRecursive(KeyWord;N) =

// gets the matching ToolTip based on the keyword

// RECURSIVE!

Let(

[

  TO = Get(LayoutTableName);



  kw = Case(

    TO="HelpDesk" ; GetNthRecord(ToolTip X HelpDesk::Keyword; N) ; 

    TO="Reports" ; GetNthRecord(ToolTip X Reports::Keyword; N)  ;

    GetNthRecord(ToolTip X Worker::Keyword; N) 

  ) ;

   

  tip = Case(

    TO="HelpDesk" ; GetNthRecord(ToolTip X HelpDesk::ToolTip; N) ; 

    TO="Reports" ; GetNthRecord(ToolTip X Reports::ToolTip; N)  ;

    GetNthRecord(ToolTip X Worker::ToolTip; N)

  ) 



]

;

Case(

  KeyWord = kw; tip;

  N > 1 ;  GetToolTipRecursive(KeyWord;N-1);

  N ≤ 1 ; "ERROR¶¶ToolTip for keyword '" & KeyWord & "' missing.  ¶Please alert the database developer."



)



)

It's not terribly elegant, but it works.

[Razumovsky]

Sorry, wasn't terribly detailed in my first reply. The value lists would be based on the Glossary table where the data was stored. Each list would have 'display second value' and 'sort by second value' selected, with the second value the same for both the Word and the Definition lists.

Then your calc would be something like:

Let([

pos=position(¶&valueListItems(get(filename); "word")&¶; ¶&MatchWord&¶; 1;1);

val=valueCount(left(valueListItems(get(filename); "word"); pos)];

getValue(valueListItems(get(filename); "definition"); val)

Edited June 23, 2006 by Guest
added ¶'s to position search

[xochi]

Your solution seems better than mine -- yours only requires creating 2 value lists, whereas mine requires adding one TO + Relation per major table, and then I have to custom-script all the TO relations.

[Razumovsky]

Hey now, that's pretty slick. I was caught up with the calcs and all and didn't even realize we were building a tool tip library. This would be great as a separate file module with the Glossary table and value lists in it. Just plug in a file reference from whatever DB, and off you go.

I had been using this sometimes as an autoenter in place of lookups when I wanted to cut down on RG clutter.

If we are optimizing this, only one of the value lists needs to have the 'display values from a second field' and sort on that value, which would be the field of the second list.

Cheers