FMP image filename relation (ignoring file extension on comparison?)

[Bryan]

Using this for tv/feature film tracking.

I have a bunch of scene card images I want to relate to scenes. I have an images database with just thje image itself and the filename (auto filled on import of folder)

I have an images table because i want to us it for multiple things, such as SCENE CARDS, and also THUMBNAILS for each shot in the database.

Scenes related from Scene numnber.

Ex: 19.jpg (this would be scene 19. Scene "19" is stored in database matching that name)

Shots relate from TAPE_NAME column in database.

Ex: A003C003.jpg (would be TAPE_NAME A003C003)

 

I'd like to see if there's an easy way to relate without having to duplicate that data into another column stripping off the file extension. I figure its something with the filter portal listings calculation.

 

I used to create php/mysql database's so the concepts of the database I'm fine with, I'm just very new to FMP and not sure WHERE to place this stuff and how the syntax looks.

 

I tried something like this:

Scenes::Scene Number = Left(Images 2::filename; 4)

But aint workin.

 

I don't think it would

Image url of database layout:

https://ibb.co/YQR2ynk

 

The relation WONT work with filename since it has jpg on it. Is there any easier way to make this work?

maybe if I have to, an easy way to duplicate all data from filename into ref_str but without the .jpg file extension? that would make it work.

 

 

 

Thanks in advance! 

Or another way to look at it... Is there any simple way to blast all the data in filename tab into ref_str, and then strip ALL those ref_str values of .jpg ? that would solve my problem

 

Image showing what i'm dealing with:

https://ibb.co/M56qzK1

 

[comment]

31 minutes ago, Bryan said:

I'd like to see if there's an easy way to relate without having to duplicate that data into another column stripping off the file extension.

No. A relationship must be based on matching (or comparing) fields. You cannot use a calculation in the definition a relationship.

OTOH, defining a calculation field that removes the file extension is easy and doesn't add much to your schema. If the extension is always .jpg (or at least it's always exactly 4 characters long), then use simply =

Left ( filename ; Length ( filename ) - 4 )

 

[Bryan]

AH, perfect! That's all I needed was a calculation field. Now I see where you put the scripting. Gotcha.

 

Thank you! problem solved!

oh only problem with that is that now after adding the calculation field... importing new records show up all blank!

 

I added more field so that thumbnail and filepath would populate and not try to force data into "ref"

 

(ref is the calcuation field using that script you posted above)

 

https://ibb.co/0m6FYD5

 

 

but once it imports them all those fields are blank now... 

https://ibb.co/vhBkKtT

 

Very strange. But I can delete the calculation field, import, then re-add the calculation field and then it works... hmm

 

I don't want to have to do that on each import and re-establish table relationship each time.. blahhh. am I missing something?

Actually nope, ever since I made the calculation field filename is no longer populating. blahhhh. Restarted FMP, still importing folder of images with blank filenames, even though the data is aligned on import.

[Bryan]

----------

Solved. I just learned about the TINY TINY TINY icons of how data pairs on import. The arrow to match the field was set to off.

[comment]

8 minutes ago, Bryan said:

Now I see where you put the scripting.

Uhm, not quite. Calculations are one thing, scripting another.

[Bryan]

Gotcha. Well regardless my current need is up and running. I'll do some tutorials on the separate subject of scripting later on when i get a moment. Thanks for getting me going