Select value from multiple values

[LaRetta]

Today is my birthday so my treat to myself is playing with several custom function concepts that I've wanted to perfect.  An fp7 file attached shows example data I'm working with:

 

Rules:

• I want the value immediately following the 'select' to the end of its line
• The items may not be in order
• No consistency in number of items in the group
• Groups always separated by two carriage returns (this is the only real consistency)
• No characters, spaces or carriage returns prior to the text string nor after the end of it
• The 'select' can have colon or not or space after colon or not so it can't be depended upon or it might not have a colon at all

Sample data:

Name:Churchill Electronics

Address:38400 Denver Street

City:Denver

State:CO

Zip:80123

 

Name:Charles Lucas

City:Seattle

State:WA

Zip:99314

 

Name:Mary Brown

State:CA

 

Zip:97401

City:Eugene

 

Result expected with the select being 'city:':

Denver

Seattle

Eugene

 

Current approach:

Let ( [
label = Proper ( select ) ; 
valueNum = PatternCount ( Left ( text ; Position ( text ; label ; 1 ; 1 ) ) ; ¶ ) ;
value = Substitute ( GetValue ( text ; valueNum + 1 ) ; label ; "" ) 
] ;
value 
&
Case ( PatternCount ( text ; label ) > 1 ; ¶ & selectValue (   <--- this is where it breaks down
)

I've created it several ways and the methods which I get to work are clunky or they break because I try to use ¶¶.  I would appreciate some clarity on the best approach!  Sampel file attached with basics for your convenience.

 

Thank you for taking a look!  What a fun day! 

selectValue.fp7.zip

Edited August 2, 2014 by LaRetta

[eos]

LaRetta –

 

Happy Birthday! 

 

Maybe try …

 

SelectValue ( text ; label ) =

Case ( 
  Position ( ¶ & text ; ¶ & label ; 1 ; 1 ) ;
    Let ( 
      [
      pos = ValueCount ( Left ( text ; Position ( ¶ & text ; ¶ & label ; 1 ; 1 ) ) ) ;
      res = Substitute ( GetValue ( text ; pos ) ; Proper ( label ) ; "" ) ;
      res = Case ( Left ( res ; 1 ) = ":" ; Trim ( Middle ( res ; 2 ; Length ( res ) - 1 ) ) ; Trim ( res ) ) ;
      rest = MiddleValues ( text ; pos + 1 ; ValueCount ( text ) - pos )
      ] ;
      res
      &
      Case ( 
        Position ( ¶ & rest ; ¶ & label ; 1 ; 1 ) ; 
        ¶ & SelectValue ( rest ; label )
      )
   )
)

Edit: Position () now has its parameters …

Edited August 2, 2014 by eos

[LaRetta]

Hi Oliver!  Thank you for responding!  Yep, this is the types of approaches I had come up with also.  It still feels, ahm, a bit thick.  It may be that the requirements require it be thick but I am hoping not.  This is the fun part of working on it ... looking for the logic underneath.  The only thing guaranteed is the 'select' for the start and the first carriage return after the start.  I can get all of that ... it is the last group which keeps failing me because, even without the double-carriage returns to tell me to stop iterating, I STILL need to process the final group.

 

Thank you for the birthday wish as well!  

 

I actually see how you're working this with the single returns!  I appreciate the different perspective a great deal!

 

Edited:  Corrected response

Edited August 2, 2014 by LaRetta

[comment]

Uhm ... could you clarify what would be the correct result if the input happened to be (no matter how illogical):

 

Name:Churchill Electronics

Address:38400 Denver Street

City:Denver

City:Portland

State:CO

Zip:80123

 

Name:Charles Lucas

City:Seattle

State:WA

Zip:99314

 

Name:Mary Brown

State:CA

 

Zip:97401

City:Eugene

 

---

And a happy birthday, of course! Where is that pesky party emoticon?

 

 

 

 

 

 

 

        Position ( ¶ & rest ; ¶ & label )

 

 

"There are too few parameters in this function."

[LaRetta]

Thank you, Michael. :-)

 

The result would be both cities should be pulled.  I realise my requirements are quite broad and potentially illogical .  In truth, the double carriage returns shouldn't matter at all, in fact ... I was just using them because, in most situations, they appear.

 

So I should iterate every line and forget about groups?  That would sure be easier!

It worked for me - Oliver must have corrected his calc?

Ah, the last was corrected to PatternCount() in the version I have ...

[Lee Smith]

Happy Birthday LaRetta!

       

[LaRetta]

You found it, Lee!  And thank you so much!  

[eos]

It worked for me - Oliver must have corrected his calc?

 

Yes – I thought Position () is more economical, since we're not interested in the number as such … of course I didn't transfer my correction completely over. Sorry …

 

Could we please implement into this forum a FileMaker Calculation Engine interpreter and <FMCode> tags, and have code between those tags automatically checked for syntax? 

[comment]

The result would be both cities should be pulled.

...

So I should iterate every line and forget about groups?  That would sure be easier!

 

 

I think that in view of your answer you should treat the text as an array of key/value pairs, and extract any value that is paired with the selected key:

 

ExtractValues ( array ; key ) =

Let ( [
pos = Position ( ¶ & array ; ¶ & key & ":" ; 1 ; 1 ) ;
start = pos + Length ( key & ":" ) ;
end = Position ( array & ¶ ; ¶ ; start ; 1 )  
] ;
Case ( pos ; Middle ( array ; start ; end - start  )
&
¶ & ExtractValues ( Right ( array ;  Length ( array ) - end ) ; key )
)
)

Note that this would work with any array of key/value pairs, such as:

Name=Churchill Electronics&Address=38400 Denver Street&City=Denver&City=Portland&State=CO&Zip=80123&Name=Charles Lucas&City=Seattle&State=WA&Zip=99314&Name=Mary Brown&State=CA&Zip=97401&City=Eugene

just by adjusting the delimiters.

 

---

 

EDIT:

Of course, the correct answer is to use records and a relationship...

Edited August 2, 2014 by comment

[LaRetta]

Oh!  You saw the flexibility where I was heading - where I could list anything in the 'select' (key) and it would retrieve the results.  I considered name/value pairs approach but just couldn't pin my own logic of what I wanted because I wanted many things!  I was envisioning using it in [ref] in sentences or portions of paragraphs also.

 

And the leading carriage return was part of that secret as well, as Oliver had it! 

 

Your naming is spot-on also, as always.  Cool!  And it is clean and lean!  Sometimes just nailing the requirements and getting clarity of thinking is the largest obstacle!  I've written several CFs similar to this but it is the peeling away of fluff and getting to the grit that takes expertise.  This will mean far less pre-processing!  I shall go play!  Thank you again, Michael!  

[LaRetta]

Of course, the correct answer is to use records and a relationship...

 

Yes I agree absolutely!  

 

My original notes on this CF were regarding an existing solution with notations in a manuscript where some paragraphs ended with [ref] then reference information to the end of the paragraph.  The user needed a list of these references that they can copy to paste into another application.  I thought it good to create a CF which could handle, not only this oddity, but other similar needs.  This will be handy when external data needs to be viewed, parsed or manipulated outside the standards of a normalised solution and when working in non-normalised solutions before a rewrite.

Edited August 2, 2014 by LaRetta

[Raybaudi]

Happy Birthday, LaRetta.

( I really wanted to celebrate using your name but I realized I did not know it and I could not find it anywhere on the web )

[LaRetta]

So here is the result.  Am I on track with your suggestion?  It allows all the flexibility I am looking for (see record #2).  I am not happy with my naming (again).  I ended up looking at what ExecuteSQL() uses but it didn't quite fit.  I could use delimiter for row separator but then what do I call the labelSeparator to keep it consistent and logical?

 

It breaks on record 3 however so I must not understand it yet.  I used a space for labelSeparator and both one and two carriage returns for row separator.

Hi Daniele!  Nice to see you around!!  Thank you so much!  Well, my real name is ... wait for it ... LaRetta.  

 

And yes, that is a real picture of me as well although I no longer wear glasses - I've gotten contacts!  

selectValueMOD.fp7.zip

[LaRetta]

My family has taken me to the coast and they were watching a movie on TV but I just talked my sister into going kneeboarding with me!!  I have cake to burn off!  So I'll play more calcs a bit later - possibly not until tomorrow (unfortunately).  I know record 3 is doable - 

[comment]

It breaks on record 3 however so I must not understand it yet.

 

The assumption is that a key-value pairs array has this structure:

 

{key, inner-separator, value}, outer-separator, {...}

 

AND that the separators are reserved: you cannot use a space, if the values (or keys) themselves contain spaces as in your example.

 

 

These assumptions do not hold true in your record 3, I think. Is it correct to assume that no part of the second paragraph, for example, is ever expected to be returned - no matter what the combination of key and separators? If so, you need to employ a different strategy here, say:

 

ExtractPortions ( text ; prefix ; suffix ) =

Let ( [
pos = Position ( text ; prefix ; 1 ; 1 ) ;
start = pos + Length ( prefix ) ;
end = Position ( text & suffix ; suffix ; start ; 1 )  
] ;
Case ( pos ; Middle ( text ; start ; end - start )
&
¶ & ExtractPortions ( Right ( text ;  Length ( text ) - end  ) ; prefix ; suffix )
)
)

 

ExtractPortionsCF.fp7.zip

[LaRetta]

Is it correct to assume that no part of the second paragraph, for example, is ever expected to be returned - no matter what the combination of key and separators?

 

You are correct - the paragraphs without [ref] were to be ignored. 

 

You've provided this CF style many times; I've written it also several times and I've parsed text using start & end technique - this is no surprise.  The surprise is how simple it can be if the logic is pinned.  To move from the original 4-5 working twisted custom functions I had created for this (where I tried to process in chunks) to the purity and simplicity of this resulting CF, is why I posted ... I just knew there was beauty in this beast somewhere if we could peel it open.  Both of these custom functions are valuable.  You are certainly the logic master, Michael.  

 

I really appreciate the assistance, guys!  :-)

[comment]

The surprise is how simple it can be if the logic is pinned.

 

  I stumbled upon this quote recently:

 

This is obvious, but the fact that this is obvious is not obvious.

[LaRetta]

I like it as well and I've stolen it.  I Googled it and found a very funny link.  The quote you quoted is down in the 'My Teacher said" section although it probably exists in many places:

 

http://www.tanyakhovanova.com/Jokes/jokes.html

 

My favourite is, "Assume, for the sake of clarity, that that yellow cube is a blue sphere." They could take a few lessons from you.