Self Join and record updates.

[North2Alaska]

This might be hard to describe, but I have three tables. If you look at the attachment you will see the three tables in question, PO, JOB and TASK. The table between, Task_Join is a self relation ship to TASK.

So, here is what I am trying to do. Jobs can have multiple tasks. POs can have multiple Jobs. So far easy, right? Now the fun begins. A job may cost more than the value of the PO, so some amount of the job is applied to a different PO. So, jobs can have multiple POs. But because potions of the tasks assigned to the JOB are paid with different POs, I have to keep track of which ones. So, the task has to be assigned to the PO as well. But POs can't have tasks without a job, but jobs can have tasks without a PO.

This model seems to cover most of the problems, but when I create a layout from PO with a portal from Task_Join, the calculations is not updated on the screen. If I move to another record and come back the data is there as expected.

So, the two questions I have are: 1) What do I have to do to get the calculations to calculate? 2) Is there a better data model for what I need to do?

I have been beating my head against the wall for 4 days and I have found many ways it won't work. So, I am looking for one that will.

Any help would be appreciated.

--

Jesse

1092332538-DefineDatabasefor“CA_Tracking2”.pdf

[Kurt Knippel]

I am not sure why you are using the Task table as both a main table and a join table. That is bad design and may end up confusing you...add an actual join table.

Remeber that in Filemaker you are NOT designing an ERD (although you can) you are also defining business logic.

Your ERD may say that POs have one or Jobs which have one or more Tasks, so you setup the relationship that way, which is very similiar to what you have now..

Then define all your relations that are part of the business logic, remeber that you can have multiple unrelated Table Occurance Graphs (ToG):

POs have Tasks with no Jobs.

Jobs have Tasks with no POs.

So setup new TOGs with those two business logics.

Look at the attached example...all the colored groups are business logic groups...only that grey grouping is the ERD and I do not actually use the ERD group in any of my development...it was just there for referece. I just use the colored groupings.

[North2Alaska]

Thank you for your suggestions. That may be my problem. I come from an Oracle/MySQL background. Could you give a little more detail as to what you are saying. It is making some sense, but the light bulb hasn't gone off yet.

Thanks again...

--

Jesse

[Kurt Knippel]

Basically the Filemaker relationship graph show both ERD and Business Logic. The ERD may say that POs have JOBs which have TASKs. But business logic says that sometimes a PO has TASKS but not a JOB, or that sometimes a JOB has TASKs but not a PO. You would then design the solution with at least 4 different graphs of table occurances and thier relationships.

One graph would show the ERD (not a necessity), another graph would show the normal PO->JOB->TASK relationships, another the PO->TASKS relationships and another the JOB->TASK relationships. This is like the Pink vs Blue vs Yellow vs Red vs Green graphs of Table occurances in the above example. Those are all variations on the same sets of tables, with differing relationships to each other.

[North2Alaska]

OK. That makes a more sense. Would you have an example database file I could deconstruct and devourer?