Basic advice please about relationships

[Eco Heat Renewables]

I hope someone can offer some very basic advice as I worked on this problem for many hours and think I am misunderstanding some fundamentals about relationships.

I have a contact management database and the area I need guidance is with my ToDo list -  All I want to have a layout which will only show "OPEN" tasks and exclude any that are defined as "CLOSED" based on a field titled STATUS.  A pretty fundimental and straight forward requirement for any business,  when I meet with my colleugues I need to click a button and see the tasks we need to focu on and not the entire list since the start of the database

I thought about just having a script perform a find but as there are likey going to be a lot of future layouts (by staff name by type etc.) and I could see this becoming unruly as the variations increase.  I thought building a relationship would be cool way to do this.

My initial thought was to have a new table "GLOBAL" and then have two field gOPEN and gCLOSED then on a hidden layout add a new record and enter "OPEN" and "CLOSED" in the respective fields.  My thoughts were that these global fields would be available all the time and then then I could set a relationship between the GLOBAL and TODO tables where gOPEN = STATUS.  In simple terms any record on the ToDo table where the Status field contained "Open" would appear and any with "Closed" would not.

Then the open ToDo's would appear on a layout based on TODO with a subsummary sorted by date,  only the ToDo's with the Status field as "Open" should be displayed.  This was not the case and it showed all ToDo's with all the Closed records showing also.

I tried loads of different variations but never found any success -  am I approaching this the wrong way?

I know this is a dumb question and I would not ask if I had not labored on it to the point of complete frustration.  

Incidentally I run a small business and am developing this for an in-house admin system  I am not an expert or developer just need to get a job done.  

I hope you can offer some guidance

 

[Lee Smith]

Hi Eco,

Have you reviewed the Templates included with the FileMaker Application? If not, I would start with the ones in the Business Projects Folder.

Lee

[comment]

53 minutes ago, Eco Heat Renewables said:

Then the open ToDo's would appear on a layout based on TODO

The relationship that you have created between GLOBAL and TODO has zero significance when you are on a layout of the TODO table occurrence itself. Here, task records can be either found or not found - not related or not related. If you want this layout to show only open tasks, you must create a found set of open tasks only. You can do this by performing a find, or by doing Go to Related Record from a layout of GLOBAL, or by other means - but eventually your goal is to establish a found set, not a related set.

Note that the related set can be used from a layout of GLOBAL (or other TOs related to GLOBAL) - for example, by showing it in a portal. You could even show such portal on a layout of TODO by making your relationship a self-join. But if you intend to produce a summarized report, then you must start by establishing a found set of records to report on.

 

[Eco Heat Renewables]

Thats great thanks for the guidance - I knew I was doing something stupid.  I have developed a script to find all open tasks and it works perfectly - Thanks

A followup question.  Although I now have a layout that lists all open tasks (using the ToDo table),  how would I develop a script to generate a found set of all the contacts that have an open ToDo? 

 

 

[comment]

8 hours ago, Eco Heat Renewables said:

how would I develop a script to generate a found set of all the contacts that have an open ToDo? 

You can search related fields too. So (roughly):

Go to Layout [ Contacts ]
Enter Find Mode []
Set Field [ ToDo::Status ; "Open" ]
Perform Find []

 

[Eco Heat Renewables]

Great Thanks -  I tried that first but go an error - unrelated table.  I just realized that I needed to search on the related table rather than native TODO - it works perfectly -  thanks for the advice!

 

Jim