Conditional value list for a newbie

[Alex Quinlan]

Hi,

 

I am new to the forums so firstly thank you for the fast amount of information on this forum and apologies as I know this topic has been covered to death. However, I am struggling to make heads of tales of it. I also apologies for my wording in this post as Im sure I will not use the correct terms to explain myself. Please bear with me.

 

I started to create a database and teach myself by watching tutorials and reading forums. I have made some blunders along the way and I am probably going to have to abandon or heavily modify what I have already got going. 

 

In lieu of this I have just made a new database to help myself clearly understand how relationships work, the problems with many to many relationships, join tables and conditional value lists. As all of these concepts are going to be incredibly important for my database.

 

The database is for an architectural firm. At the core of the database they will need to keep records of all their contacts, projects, the stage of the project, and billing. 

 

 

My questions is about conditional value lists. I currently have three tables: Contacts, Projects and a join table called Type. 

 

The main reason, that I can see so far, for having a join table is because the contacts can multiple types( a contractor, a sub-contractor, a consultant, a client). So the Type table is used to have multiple records for the same Contact showing what "types" they are.

 

What I am struggling to achieve is in the projects table. I am trying to have portal where I select a value from the type (i.e. contractor, sub contractor, client or consultant), in a pop up list, with a field next to it which will dynamically change only listing that type of contact. 

 

I found one way to do it online but It seems a bit messy. The idea is to have 4 auto fill fields in "Projects" with the names of all these types. Then in manage relationships you copy the "type" table creating a a new "Data source" (I think this is what its called) for each one type (i.e. contractor, sub-contractor etc.). You can then select a value from that "data source" and "Include only related values starting from:".

 

Is this the only way to do it? 

 

 

Many thanks in advance for help given. I tried not write an essay, but kind of failed.

 

Alex

[comment]

If I understand your description correctly (which is not at all certain), your Contacts table has a Type field - and so does (or should have) your Types table.

 

Now, if you define a new relationship, using an additional occurrence of the Contacts table, as:

 

Types::Type = Contacts 2::Type

 

you can then define a value list, using values from Contacts 2, show only related values starting from Types. With this in place, once you select a type in a record of Types, the value list will show only contacts of the selected type.

 

 

The thing that puzzles me is that you say that the same contact can play different roles in each project - so having this value list to restrict the choices is kind of contradicting that..

[Alex Quinlan]

Thanks for the reply.

 

Its not that a contact can play different roles in each project. Its more than in different projects that can play different roles.

 

Ie Project 1 John Doe is the Client

in Project 2 John Doe is the Contractor

 

Currently I have a Jointable with ContactsIDfk and ProjectsIDfk in relationship with corresponding primary keys in the other two tables. Also in the jointable is a new field called Types. I have portals on both the Contacts and Projects layouts with the "Types" field in. This has allowed me to create new records in that Jointable listing contacts, their role), and which project.

 

But I guess this table does nothing to help me discern what contact is "allowed" to do each role which is my problem.

 

I want to be able to select what role a contact plays. Then keep a record of on which project they play these roles (contractor, client etc.) so to list them in a layout.

 

Im going to try to get my head around what you've suggest and play with the smaller database to recreate what you've said. But are you suggesting that I create a table which is essentially a sub category? So that I can select what contact falls under each category (in this each role)?

 

Thanks again for the help

[Alex Quinlan]

Ok so i've managed to reproduce what you've said. Thank you!

 

However as you've said whats the point of having a smaller list if contacts can play multiple roles. The key part to this is that a contact can act in different roles on different projects but not different roles on the same project. Also there will be hundreds of contact so its pretty important to be able to shorten the list as much as possible.

 

Jack Black, for example, can be a contractor or a sub-contractor but he will never play different roles on the same project.

 

I would like to flag what role a contact can play within their own record in the "Contacts" table.

I would also like to select what roles were played on a project on the "Projects" table.

Finally I would like to be able to record the data of whom played what role on each project on a separate table called "JT_ProjCont". Because, as I understand it many to many relationships don't work well and I would also like to be able to generate a report on what role people played on different jobs in the future.

 

I have managed to achieve all of this apart from a way of identifying what roles the contact can play and then using that in a conditional value list on the "JT_ProjCont" table.

 

I played with having four number fields in the contact list called Client, Contractor, Sub-Contractor and Consultant. I thought perhaps I could do a IF conditional value list calculation? I thought I read somewhere you can do this but I can't find it again.

 

 

Thanks again and forgive my terminology.

[eos]

See if this sample file helps you.

Projects_eos.fmp12.zip

[Alex Quinlan]

Ok. You are an amazing person. Let me look at this and try and understand it, but it appears to do everything I have spent the last 5 days trying to achieve.

 

Thank you

[comment]

I would like to flag what role a contact can play within their own record in the "Contacts" table.

 

I believe you could use a checkbox field in the Contacts table to make a contact eligible for multiple roles. The relationship I described earlier will pick any contact that has that role checked.

 

The key part to this is that a contact can act in different roles on different projects but not different roles on the same project.

 

Actually, that makes no difference at all for the current purpose.

 

 

 

---

Oops, I see I am being obsoleted as I speak ...

Edited October 3, 2014 by comment

[Alex Quinlan]

Comment, thanks - not at all. This is all important learning for me and although EOS seems to have basically done the first slice of the project for me I real need understand EOS"s scripts that he has made to deal with my problem and move onward from there.

 

You are quite right the different roles thing doesn't make a difference. My brain was slowly melting!

 

I am going to be working on this project for quite some time as i plan to add a whole section on budgeting and invoicing, employee time keeping, notes etc.

 

Thank you for all the help.  Time for the pub.

[Alex Quinlan]

EOS, I really like how the script works and its really easy to use for the user.

 

For the most part I think I understand what it does and I've tried to recreate it. But I am struggling to use the ToggleElementInList function, and Im only guessing what it does. Is it a custom function? Am i able to use that function in my own database without the advanced edition? If not is there another method to go about this?

 

Also Ideally in the projects table I would first choose the type and then contact based on allowed types. The contact list will eventually be huge but the types will always be those 4. 

 

I am hoping to eventually add sub types but I am worried about it getting too complicated.

 

Thanks in advance

[eos]

the ToggleElementInList function, and Im only guessing what it does. Is it a custom function? Am i able to use that function in my own database without the advanced edition?

 

You can use it alright with a non-Advanced version, you just cannot edit it (which also means: you cannot copy it over into another db …). As to what it does: exactly what it says. Feed it a list of items and a single item; if the item is already in the list, it's removed, otherwise it's being added.

 

If not is there another method to go about this?

 

Except for recursive CFs (and even there is a workaround) you can always implement a CF's functionality using a regular calculation field, or a script step. The advantage of a CF is that you can stash away the functionality into a neat little package; if you need to edit the code, there's one place to do it. (And you can write recursive code.)

 

See the attached file which has the exact same functionality, but the CF's code has been implemented directly into the scripts.

Projects_withoutCF_eos.fmp12.zip

[Alex Quinlan]

I believe you could use a checkbox field in the Contacts table to make a contact eligible for multiple roles. The relationship I described earlier will pick any contact that has that role checked.

 

 

I don't know why I thought filemaker couldn't distinguish between two lines of data in one field. A revelation!! However I still can't get it to work

 

I've made a checkbox in my contacts displaying data from Contacts::Type with values from Type 2::Type.  This successfully allows me to mark multiple types and when I look at the Table View I can see the data is stored in the same field on two lines. Great!

 

When I come to trying to have the conditional values list on the Projects table I am a bit lost. I am recording the data in my jointable "JT_ContactsProjects" so I have a portal to that table from my "Projects table".

 

In this portal I have a Type field in a pop-down with values from Type 2: Type. I then try and set a contact field in a pop-down and am failing to make the conditional value list. I added the relationship you suggested but then added another to make it -

Type 2::Type = Contacts 2::Type = JT_contactsProjects::Type

I have the contacts pop-down with values from Contacts 2::__ContactsIDpk and values only related to Type 2::Type. But, although it seems to limit the list I get false positives.

 

Im quite confused - sorry if i'm making a glaring error.

[Alex Quinlan]

 

 

See the attached file which has the exact same functionality, but the CF's code has been implemented directly into the scripts.

 

Thank you, I have managed to incorporate it into my database.

 

Is it possible to reverse the way the conditional value list works on the Projects table?

Ie Select the Type first then have a list of names who can fill this role? Rather than the other way around.

 

I'm trying to work it out, I need to make a relationship with allowedTypeIDs somewhere I think!?!

[eos]

I'm trying to work it out, I need to make a relationship with allowedTypeIDs somewhere I think!?!

 

Good guess …  

 

Add a selector field to your Projects table, then add a relationship between that field and a TO of Contacts, where

 

Projects::typeSelector = Contacts_forAllowedTypes::allowedTypeIDs

 

Now you could add a portal of Contacts_forAllowedTypes to the layout.

 

To script the creation of a new ProjectContact, you just need to check if the person you want to add by clicking on a portal row has already been added to the project (which, as per your business rules, prohibits additional involvements), e.g. with

not IsEmpty ( FilterValues ( Contacts_forAllowedTypes::contactID ; List ( ProjectContacts::contactID ) ) )

If the contact passes the check, grab the Contacts_forAllowedTypes::contactID, the selected typeID and the projectID, and you have all the ingredients to create a new ProjectContact record.

 

You could of course use a calc field like List ( ProjectContacts::contactID ) within the relationship

 

Projects::typeSelector = Contacts_forAllowedTypes::allowedTypeIDs

Projects::listOfContactIDs ≠ Contacts_forAllowedTypes::contactID *

 

to only match contacts via this relationship that are of the correct type and are as of yet uninvolved in the current project; saves you the check in the script (at the cost of a new field).

 

* Be aware that this doesn't work if no-one is involved yet; the list will be empty, and a Filemaker relationship cannot use empty fields in a relationship (as of FM11). You can amend the calc field as

 

Let ( lc = List ( ProjectContacts::contactID ) ; Case ( not IsEmpty ( lc ) ; lc ; 0 ) )

 

Or try adding a portal filter with 

IsEmpty ( FilterValues ( Contacts_forAllowedTypes::contactID ; List ( ProjectContacts::contactID ) ) 

and see if this approach is sufficiently performant (i.e. not too slow).

[Alex Quinlan]

Good guess …  

 

 

Unfortunately this is all a bit beyond me.

 

Im confused as to why I would create a field in Projects. Is it a global field to be used as part of the script?

 

Also If I am not directly making a record and have to use a script to create the record. Then would the script be on the "Contact" field? 

Would this all be from the portal Contacts_forAllowedTypes?

 

Thanks

Showing my severe ignorance.

 

EDIT. 

 

Ok so with the use of the typeSelector in Projects and a portal to Contacts_forAllowedTypes I have at least made the relationship work. However the portal only lists what the selector is set to. Do I then need to have a button next to each person running the create record script?

 

Is it not possible to have a single line with an add function perhaps?

[eos]

Im confused as to why I would create a field in Projects. Is it a global field to be used as part of the script?

 

Because you're trying to say “show me only contacts of that type”, and since you want to drive a relationship, and since relationships works via match fields, you need a field to hold “that type”.

 

This works without any script; but assuming that you're not displaying those contacts for the heck of it, but want to add one of them to the project, that's where the script comes into play.

 

Then, of course, you would use that type in that field in the creation of the new ProjectContact record.

 

Have a look at this amended file.

 

PS: As you can see, a button is what you make into one … 

Projects_withoutCF_eos_v3.fmp12.zip

[Alex Quinlan]

Ok great - I wasn't sure exactly how global fields function but I have a better understanding now. I can't imagine myself writing that script - perhaps by the end of this project I will be able to

 

If i'm understanding this correctly then the  "#one way" part of the script would work without a portal to ProjectContacts, is this correct?

 

Also could you help me understand this line "Let ( mod = Get ( ActiveModifierKeys ) ; not ( mod = 10 or mod = 8 ) )" please.

 

And what does this do?

 <<$l>>

 

 

Thanks again for all this help. 

[eos]

If i'm understanding this correctly then the  "#one way" part of the script would work without a portal to ProjectContacts, is this correct?

 

Yes; you can always create new records in any table by switching to a layout based on a table occurrence of that table (and thus changing your context). A portal is the only way to viewing multiple related records, but never required to create them.

 

Also could you help me understand this line "Let ( mod = Get ( ActiveModifierKeys ) ; not ( mod = 10 or mod = 8 ) )"

 

As a general tip: you can re-engineer calculations by working your way from inner to outer and looking at the ingredients, i.e. the functions involved.

 

Let() lets you define variables, which in this case means I have to write the next function with the longish name only once, and then can refer to the variable instead.

 

Get ( ActiveModifierKeys ) returns a number that is the sum of the codes of the modifier key(s) that were pressed at the script start; 8 is alt/option, and 2 is Caps lock. That value is stored in (assigned to) the Let() variable 'mod'

 

So if you pressed the alt key (with or without Caps lock), the calc in parens returns True (because the Let() variable 'mod' then does hold either 8 or 10), which the “not” operator turns into False; consequently …

 

• the entire calculation evaluates to False

• the If[] block is not entered, meaning …

• you don't see a confirmation dialog in which you could change your mind …

• and the portal row is deleted without further (or any) ado

 

In plain English: by pressing alt/option while launching the script (clicking the button), you can suppress the dialog and delete the portal row directly (fit for the legendary ”experienced user“).

 

And what does this do?

 <<$l>>

 

That's a bit harder to explain; <<someFieldOrVariableReference>> is the code to use merge fields and variables on a layout; for variables, this is usually the prerogative of global $$variables.

 

$i, OTOH, is a local variable, used as a merge variable within the space of an individual portal row, and it is defined within the Conditional Formatting calculation of the name field in the same portal row (have a look) – all of which makes this into a little hack.

 

That functionality is not really supported or documented, because $vars are supposed to exist only within the space of a script – but obviously, you can give them an existence within other calculation spaces.

 

It is nice to have anyway, because it lets you perform and display calculation results on the fly, without having to create dedicated fields just for that purpose (a Web Viewer and a bit of HTML is also handy, but WVs don't work in a portal …)

[Alex Quinlan]

Thanks for the explanations. Really helpful - although i think I have a lot to learn about scripting. 

 

I have realised, having spent a lot of time trying to figure this out, I shouldn't be using multiple roles. It means that I would then have to make sub categories as well. Its easier, for me, and makes more sense when inputting and working with the data to have more specific roles from the offset- Pool consultant, Structural engineer etc..

 

I am sure I will use parts of what i've learnt to use in the future however.

 

Thanks again