Jump to content

Find Field via Relationship


Recommended Posts

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!

Link to post
Share on other sites
Posted (edited)

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 by comment
Link to post
Share on other sites

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.

Link to post
Share on other sites

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?

 

Link to post
Share on other sites

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).

Link to post
Share on other sites
Posted (edited)

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

Edited by sirsancho09
Double text
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.