Conditional Relationships is it possible?

[dkey]

Hello

I have several db which we use to edit films

Unfortunately each clip has more than one names according to the various people who looked at it hence the original clip name field is no longer available

I wonder if it is possible to create relationship smething like a "conditional relationship" (if this is the proper way to call it)

I have a Main DB with every various fields. Each field has the various names assigned to the original clip

If I want to relate DB2 to Main DB is there a way so I can conditionally create the relationship

In other words

DB2 will relate to The MAIN DB if one of its fields matches a selected field in the MAIN DB

Something like this

IF CODE 1 in MAIN DB matches nothing in DB2

GO to CODE 2 in MAIN DB until it finds the Matching field there and relate the 2 databases

There might be a simpler way to do so maybe but I am not too skilled hence if there are other ways a sample FMP12 example would be great, googling I found no solution and any help will be highly  appreciated

Regards

Dan

Edited July 3, 2015 by dkey

[eos]

Rather than having “various fields for the various names”, you should have a separate “ClipNames” table in which you store all names/titles for a given clip as related records (i.e. one related record per name).

Then you can relate your DB2 to the “ClipNames” table; since ClipNames only has as single title/name field, there is only one field to match; you can check from Clips if any related ClipName matches a record in DB2.

See if the attached sample file helps you.

 ClipNames_eos.fmp12.zip

[dkey]

Thanks I downloaded your example and tried to figure it out

I  set back the serial number to auto-enter at one both in the DB2 Table and in the ClipName Table then imported the clipnames in the ClipName table and in the DB2 table leaving all file names and the rest as it is in your file.

However looking at your relationship window the "name" and the "code" field are different therefore the tables dont relate.

I am still trying to figure out a way to relate db (or Tables) ..

 

[eos]

I am still trying to figure out a way to relate db (or Tables) ..

You have that way in front of you. Maybe try harder? 

The essential thing to understand is how a table with related records replaces your numerous name fields and is used to match against another table; if any of the clip's name matches, so, by extension, does the clip.

However looking at your relationship window the "name" and the "code" field are different therefore the tables dont relate.

I have no idea what you mean by that.

Why don't you try to create a new file from scratch and re-create these relationships? Once you see how it works, you can try to implement it into your system.

btw, it doesn't matter if “DB2” (does that have a real name?) is a table in the main or another file; read in the documentation how to define “External Data Sources” that allow you to treat a table in an external file exactly like an internal one. 

[Lee Smith]

Hi Dkey and welcome to the forum,

Although FileMaker has a reputation of being easy to use and learn, it still has a learning curve. The learning  curve can range from easy to very complex depending upon what you want to accomplish. All we ask our members to do, is to have a basic knowledge of FileMaker and its functions.

The more complex your solution is, the more time you’re going to have to commit to learning the different functions, scripts, etc.

 The basic skill levels can be learned from reading the users manual, Online Help and working with the example files that came with your copy of FileMaker Pro.

You might find the information I posted in this link here helpful.

 

Edited July 3, 2015 by Lee Smith

[comment]

    Something like this

IF CODE 1 in MAIN DB matches nothing in DB2

GO to CODE 2 in MAIN DB until it finds the Matching field there and relate the 2 databases

This is sort of possible, if you define a calculation field in MAIN DB  (result is Text) =

List ( CODE 1 ; CODE 2 ; CODE 3 ; ... ; CODE N )

and use this field as the matchfield for the relationship to DB2.

However, this is not a good method and you shouldn't be using it other than as a gap stop until you straighten out your data structure. In general, you should never have numbered fields.

Note also that in most cases you want to base a relationship on a field that's unique in the parent table. Here you have a good chance of two clips sharing a name - and thus being related to the same record/s in the DB2 table.

Edited July 3, 2015 by comment

[dkey]

Hi Dkey and welcome to the forum,

Although FileMaker has a reputation of being easy to use and learn, it still has a learning curve. The learning  curve can range from easy to very complex depending upon what you want to accomplish. All we ask our members to do, is to have a basic knowledge of FileMaker and its functions.

The more complex your solution is, the more time you’re going to have to commit to learning the different functions, scripts, etc.

 The basic skill levels can be learned from reading the users manual, Online Help and working with the example files that came with your copy of FileMaker Pro.

You might find the information I posted in this link here helpful.

 

Thanks Lee

While I certainly am no expert as many people are, I used FileMaker ever since it came out nad before Apple bought it. I manage to do even rather complex things with it: Film editing for one within FMP itself with some Applescript support, as what I get gives me better results than most full time video editing software. Also in this case I asked people around and got some meaningful help. File Maker Pro for video editing sounds crazy, but indeed it isn't. Of course most solutions, formulas and workflowsì I don't know and never use and many others I use badly, but then I work in the european cinema world, where specialisation is not an absolute plus, while an holistic view in projects and working in group often allows interesting results. To make films I need to be able to talk to various professionals, and I need to learn enough of their skills to ask proper questions and find mutual interesting results. 

I've learned to understand the core of various software solutions in various fields, and even intended to achieve totally different results, as occasionally solutions pop out unexpectedly if one has the ability to relate things which might appear totally non related.

Certainly everything can be do done a lot better than the ways I use, but, forgive me, manuals are very good for real beginners or people who uses certain software as job or a full time experience ... in my case I asked from experienced and kind people as your self if there were options to solve my problems. All this after trying on the net to see if anyone had the same problem I did. 

For this I am very grateful to the ones who spent precious time reading my query and giving me suggestions, especially the "consultant" (I don't know his name) as his solution gave me what I needed. 

Thank you anyway for your suggestions and time. 

Edited July 3, 2015 by dkey