Find Field via Relationship

[sirsancho09]

I have a database setup wherein I take post-production notes on an animated series. I have created a relationship where a table occurrence of my main table (retakes) named (retakes_listfind) is present. This is a relationship where a text field named "listfind" in retakes_listfind is set to equal a text field named "scenenumber" in retakes.

I can paste a list of incoming scenes (1, 2, 3, 4, 5, etc.) in a retakes_listfind layout with a portal to retakes. Here is the issue. It works fine, but only if the numbers match. Due to an accounting requirement we need to enter all scene numbers with a leading zero, and it isn't a pure number because often scenes have text additions for inserts (e.g 004A between 004 and 005). However when i receive my lists from excel they do not have that leading zero. I can manually create a formula that formats out right, but I would rather make it so if I paste 4 in the "listfind" scene, "004" will show up. I was able to create a separate field name "listfind_scenenumber" where the scenes are dialed down to numbers without zeros with an Int calc, but this ignores the letter and anything with leading zeros (so 4A above would show 004A and 004-not that big a deal I can work with that but putting it there for context).

I would like to be able to enter either 4 or 004 and have the record show up. If I match it to both fields in the relationship it only seems to show exact matches again (essentially ignoring the match on "listfind_scenenumber") It's such an oddly specific thing, and I coded the original use of this in another database an age ago that I am hazy on the nuances. Any help would be useful. I am not married to this method, so if something has changed in the last few versions (on 15 now) I am happy to switch.

 

Cheers!

[comment]

Why don't you define the field to auto-enter the leading zeros? Say something like:

SerialIncrement ( "000" ; Self ) & Filter ( Upper ( Self ) ; "ABCDEF" )

Then, if you enter "4", it will auto-correct to "004", "4a" will become "004A", and "004B" will be unchanged.

--
P.S. Please update your profile to reflect your version and OS, so that we know what you can use.

 

 

Edited May 13, 2020 by comment

[sirsancho09]

That is a good solution, but I failed to mention that I am pasting lists from excel in the "listfind" field to get multiple results in the portal. So something like that will turn a list like:

5

9

10

12

To 591012. Sorry I failed to mention that.

[comment]

Then you need to loop over the values of the list and pad each one individually. You could use a script for this, or a custom function, or the While() function new in v.18. Did I mention we need to see your version in order to know which suggestions you can use?

 

[sirsancho09]

Sorry hopefully it will show now. I'm on 15 Pro Advanced. If there is a direction or the concept of the direction it would be ideal if it worked there. However it could also me the means to get my company to update us (not likely in this current world).

[comment]

I believe a triggered script would be the easiest to implement. See the attached demo.

 

AutoPadValues.fmp12

[sirsancho09]

Perfection! Thank you for the elegant solution to my brute force methodology.

Edited May 14, 2020 by sirsancho09
Double text