Regexarray(, regexreplace(, and multiline text fields

Panorama X
1

Hello. Long-time programmer (BASIC, Pascal, COBOL, C, …) and database developer (FileMaker, Postgres, …) here, exploring Panorama for the first time. Quite impressed, so far.

I have a text field (“Actors”) that contains multiple lines, thus:

                        - Cillian Murphy (Thomas Shelby)
                        - Sophie Rundle (Ada Shelby)
                        - Paul Anderson (Arthur Shelby)
                        - Helen McCrory (Polly Gray)
                        - Aimee-Ffion Edwards (Esme Shelby)
                        - Joe Cole (John Shelby)
                        - Ian Peck (Curly)
                        - Tony Pitts (Sergeant Moss)
                        - Colin Connor (Black Lion Barman)
                        - Harrison Vaughan (Black Lion Boy)
                        - Harry Kirton (Finn Shelby)
                        - Simone Kirby (Irene O’Donnell)
                        - Rory Keenan (Donal)
                        - Richard Patterson (Condemned Man)
                        - Paul McEwan (Governor)
                        - Daniel Fearn (King Maine)
                        - Ned Dennehy (Charlie Strong)
                        - Natasha O’Keeffe (Lizzie Stark)
                        - Jane Wood (Medium)
                        - Sam Battersea (Woman at Seance)
                        - Rory Gallagher (Eamonn Duggan)
                        - Alastair Barley (Eden Club Doorman)
                        - Dorian Lough (Mario)
                        - Richard McCabe (Winston Churchill)
                        - James Boyland (Communist Comrade)
                        - Tom Mothersdale (Sabini Kidnap Gangster)
                        - Noah Taylor (Darby Sabini)

I’d like, I think, to use this as a text array, but to reformat the data so that it’s stored like this:

Cillian Murphy: Thomas Shelby
Sophie Rundle: Ada Shelby
Paul Anderson: Arthur Shelby
Helen McCrory: Polly Gray
Aimee-Ffion Edwards: Esme Shelby
Joe Cole: John Shelby
Ian Peck: Curly
Tony Pitts: Sergeant Moss
Colin Connor: Black Lion Barman
Harrison Vaughan: Black Lion Boy
Harry Kirton: Finn Shelby
Simone Kirby: Irene O’Donnell
Rory Keenan: Donal
Richard Patterson: Condemned Man
Paul McEwan: Governor
Daniel Fearn: King Maine
Ned Dennehy: Charlie Strong
Natasha O’Keeffe: Lizzie Stark
Jane Wood: Medium
Sam Battersea: Woman at Seance
Rory Gallagher: Eamonn Duggan
Alastair Barley: Eden Club Doorman
Dorian Lough: Mario
Richard McCabe: Winston Churchill
James Boyland: Communist Comrade
Tom Mothersdale: Sabini Kidnap Gangster
Noah Taylor: Darby Sabini

I’ve constructed a regex that works in the Formula Workshop:

regexreplace(Actors, "\ *-\s(.*)\s\((.*)\)", "$1: $2", 16)

I’d like for it to work automatically on the field, though, whenever I paste data into it. It looks like the way to go about this is to use the function in the “Code” panel in the Field Properties for the Actors field. However, when I paste data into the field, no change takes place. I thought perhaps that I’d misunderstood how this was supposed to work, so I replaced the regexreplace( function with a simpler replace( function, which indeed halved the number of leading spaces when I pasted the original string into the field:

replace(Actors, " ", " ")

so I think I must be on the right track with “Code” panel. But why won’t regexreplace( work here for me? Seeing as I’m working with multiple lines, I tried various expressions with regexarray(, but never saw any results at all.

Can someone point me in the right direction? Many thanks!

2

Put your regex in the formula pane - not the code pane.

1 Like
3

It could also work in the code pane, but you would need to write it as a procedure statement, rather than a formula.

Actors = regexreplace(Actors, "\ *-\s(.*)\s\((.*)\)", "$1: $2", 16)
1 Like
4

Perfect! Thank you, @gary and @dave!