Calc to show the Client of a Project

[Alex Quinlan]

Hi,

 

I am trying to have a calculation show the name of a contact if they are a client on a certain project. The database is has 4 tables.

 

Contacts>--------ProjectContacts---------<Projects

   ⌄                   ⌄

      |                                   |

Types----------------------

 

 

ProjectContacts holds records for which contacts worked on each project.

 

Types stores the different types of contacts.

 

I am very new to filemaker and tried to butcher someones script into what I wanted but have failed. In fact my efforts were so bad filemaker wouldn't even let me click ok. Serves me right!

 

If
(
// Test
ProjectContacts::Id_type=1 ;    //Type - 1 is Client
 

// Result if true
"GetAsText (  Contacts::Fullname ) ;

// Result if False
"You have yet to select the Client"
)

 

 

Any thoughts as to the best way to go about this? Many Thanks in advance.

 

 

 

[Kris M]

Maybe better if you could post the file

[Alex Quinlan]

Attached zip.

 

 

I started with the contacts starter and then have added to it.

 

In Projects Im basically trying to have the name of the client on the left sidebar and have it click through to their contact details layout record.

 

Thanks for any help.

MQA_v1.fmp12.zip

[Alex Quinlan]

I have managed to achieve this but in a really bad way. 

 

In ProjectContacts I made a auto enter lookup field for the fullname in the related Contacts table. I've then put that field on the Projects layout which, as long as the client is the first name recorded for that project id, then it shows the name.

 

I'd much rather have a more elegant solution to this. Also i'm sure once I know how to do this I can reuse the knowledge in the future.

 

I also noticed I had a relationship between title and contact_ID in error. Oops!

 

Alex

[eos]

See attached file (using a filtered portal and merge fields); also note the Contacts value list, and the fact that you can use a value list with an ID and a human-readable value and opt to only show the latter, while using the former.

MQA_v1_eosMOD.fmp12.zip

[Alex Quinlan]

Thanks Eos.

 

The <<Type::Type>>: <<Contacts::Fullname>> is in a text field I believe? But I cannot see where the portal filtering is taking place. Im baffled, as usual.

 

Edit - ok I get the merge field. But still not sure where or how the portal filtering is taking place. I can't even see a portal :/

 

The contact value list i used before, contact_roles also used the ID and then showed fullname. Is this what you were referring to?

[eos]

Edit - ok I get the merge field. But still not sure where or how the portal filtering is taking place. I can't even see a portal :/

 

Look closer, there is one alright … 

[Alex Quinlan]

Haha so sneaky

 

Great thanks Eos. By the time I'm finished with this you will have done the majority of the work