Parent Relationships and Child Relationship Tabs

[Aoirthoir]

This is going to be short and sweet, because I thought I had already posted it. But I must have hit the close tab at some point. So here we go:

I currently have 4 tables of import. R_ (relationships), D_TASK, D_NOTE, D_SUGGEST (D_ means data). Every D_ has a field named ID, primary key, auto increment, mediumint, not null, unsigned, zerofill. R_ looks like this:
-----------------------------------
CREATE TABLE `R_` (
`PNAME` varchar( 10 ) NOT NULL,
`CNAME` varchar( 10 ) NOT NULL,
`PID` mediumint( 8 ) unsigned zerofill NOT NULL,
`CID` mediumint( 8 ) unsigned zerofill NOT NULL,
`OID` mediumint( 8 ) unsigned zerofill NOT NULL default '00000001',
PRIMARY KEY (`PNAME`,`CNAME`,`PID`,`CID`,`OID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
------------------------------------
P always means parent. C always means child. O is order.

My tables/D_TASK/relationships.ini could look like this:
------------------------------------
[D_TASK]
action:label = Child Tasks
action:description = Other tasks which must be completed before this task can be marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.CID
AND R_.PID='$ID'"

[D_TASK]
action:label = Parent Tasks
action:description = Tasks which cannot be completed before this task is marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.PID
AND R_.CID='$ID'"

[D_NOTE]
action:label = Task Notes
action:description = Notes about this particular task.
__sql__ = "
SELECT * FROM R_, D_NOTE
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_NOTE'
AND D_NOTE.ID = R_.CID
AND R_.PID='$ID'"

[D_SUGGEST]
action:label = Suggestions
action:description = Task specific suggestions.
__sql__ = "
SELECT * FROM R_, D_SUGGEST
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_SUGGEST'
AND D_SUGGEST.ID = R_.CID
AND R_.PID='$ID'"
-------------------------------

Yes you saw correct. TWO self relating relationship tabs. Of course that wont work because dataface will read in the first tab, then when it reads in the second, it will override the first. The important thing here is that both work. Thus you can have forward and backward relationships.

Ok...but is there a way to work around this for now? (Why would I ask?) Of course there is. And this is the code in tables/D_TASK/relationships.ini
-------------------------------
[D_TASK]
action:label = Child Tasks
action:description = Other tasks which must be completed before this task can be marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.CID
AND R_.PID='$ID'"

[S_DUMMY]
action:label = Parent Tasks
action:description = Tasks which cannot be completed before this task is marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.PID
AND R_.CID='$ID'"

[D_NOTE]
action:label = Task Notes
action:description = Notes about this particular task.
__sql__ = "
SELECT * FROM R_, D_NOTE
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_NOTE'
AND D_NOTE.ID = R_.CID
AND R_.PID='$ID'"

[D_SUGGEST]
action:label = Suggestions
action:description = Task specific suggestions.
__sql__ = "
SELECT * FROM R_, D_SUGGEST
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_SUGGEST'
AND D_SUGGEST.ID = R_.CID
AND R_.PID='$ID'"
---------------------------------

The S_ is always a system table for me. Dummy is just that, a dummy table. So as not to cause conflicts with either SQL or dataface, it has one field, ID, all specs as listed above. So this works and you get relationships both ways..and tabs that say parent and child (or whatever else you put on the label).

A caveat of course is, your add relationship to this links say...add S_Dummy. Currently I dont know if this can be changed in a dataface ini or not. I imagine it can be changed in smarty or in the delegate classes. I am just getting into these things so I dont have any suggestions for you for that.

But there you go.

[shannah]

In a more recent version of Dataface (it should be in 0.6.2 and later), I have added a similar type thing. Check out this relationships.ini file taken from a "Pages" table which is meant to represent a heirarchy of pages.

Code: Select all

[children]
__sql__ = "select * from Pages where ParentID='$PageID'"
meta:class = children
metafieldsrder = PagePosition

[parent]
__sql__ = "select * from Pages where PageID='$ParentID'"
meta:class = parent


This is meant for tree structures. I.e. the parent relationship will only ever worry about the first record in the relationship. There is no tab displayed for the parent relationship. The breadcrumbs will reflect this relationship.

Also a couple of methods were added to Dataface_Record:

getParent() : returns Dataface_Record object that is the parent of a record.
getChildren() : returns array of Dataface_Record objects that are children of this record.

Is this sort of what you're looking for?

[Aoirthoir]

Ok I have to experiment with this. But this is exactly what we were looking for. Especially if the tree can all be displayed together.

As you know I was in shipping when I came to this company. I just lucked out because I programmed years ago. But never got a degree. So they have a network consultant, who actually is a really good guy. Last year, before I came they put together an RFP for the program we are creating now. When my boss figured out what I knew, he had this fella interview me to make sure I really knew it. So he was satisfied. Now he acts as a translator from me to my boss when I start getting a little more techspeak.

Anyhow, he wanted a gantt chart program to keep track of these things. We had considered using MS Project. But the price was outrageous. Plus I would have to go through loops to acquire the data. So I decided to put it into dataface. The problem is creating the gantt. (I had tried experiementing with open source php based mysql project management software). All of it was a waste. ALL I need basically is a task list. Some additional things like notes, so I can note, code changes, HOWTOs and so on. But all of these programs required me to state our company hours, OT, hourly pay, bla bla bla. Anyhow I just ended up doing it in dataface. The gantt was the hardest part. So I resorted to a copy/paste in Excel. Using conditional formulas.

So kind of the last piece to the puzzle was this tree structure...

So if record A has a child B which has a child C and also has a child D...will all of those dispaly for Record A?

Also..am I accurate in understanding that PAGEID is your autoincrementor and PARENTID is the self mapping relationship field contained in the same table PAGES? Lastly, Does this mean that each page can only have one parent? (We might have cases where a certain task can be under multiple projects...)

[shannah]

Well, the tree idea will have to be expanded upon greatly. I have implemented a treetable for use on relationship tabs that have this specified.. but right now it only goes 3 deep.. the plan was to ajaxize it, but no time to tinker with that yet. You can use a treetable (instead of a normal list) in a relationship tab by specifying the list:type = treetable parameter in the relationships.ini file as follows:

Code: Select all

[pages]
__sql__ = "select * from Pages where ParentID='$PageID'"
action:label = "Web Pages"
metafieldsrder = PagePosition
list:type = treetable


This is a relationship from another table to the Pages table to show the page heirarchy in that record.

This set up is limited to a single parent. If there is more than one parent then it is a graph and not a tree - and a treetable component no longer handles it. Probably just 2 separate relationships are better for the multi-parent model (one for parents and one for children). If you want things displaying on the same page, I generally just override the view tab contents using the block__view_tab_content() method - or you could create another action that shows both relationships and hide the automatically generated relationships tabs using action:visible = 0

-Steve

[Aoirthoir]

Mr. Steve,

I've not tried out your example yet. Been to busy. And am wondering what the display looks like so I might give it a try this evening. Now I've made a couple of changes. Because I wish to also be able to see siblings. Having the parent tab has greatly reduced my data entry. When I am finished editing a particular record, I dont have to display the list and narrow it down anymore. Now I just click Parents, and get a list of the parents I need to head for. Usually though there is only one. Also I am dealing with multiple layers deep. A task my depend on another task before it can be marked done, down many levels. Now, I am trying to set up siblings. A third tab with a second dummy table. I have renamed my dummy tables as DUMMY_Whatever. This way I can have additional ones when needed. Here is my relationships.ini:
=====================================
This Works:
[D_TASK]
action:label = Children
action:description = Other tasks which must be completed before this task can be marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.CID
AND R_.PID='$ID'"
actionrder = 1
----------------------------------------------
This also Works:
[DUMMY_PARENT]
action:label = Parents
action:description = Tasks which cannot be completed before this task is marked done.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.PID
AND R_.CID='$ID'"
actionrder = 2
actions:addnew = 0
actions:addexisting = 0

--------------------------------
Of course, this doesnt exactly work, as it will just give me every record for which there is a PID existing.
[DUMMY_SIBLING]
action:label = Siblings
action:description = Tasks which belong to this Task's parent.
__sql__ = "
SELECT * FROM R_, D_TASK
WHERE R_.PNAME='D_TASK'
AND R_.CNAME='D_TASK'
AND D_TASK.ID = R_.PID "
;AND R_.CID='$ID'"
actionrder = 3
actions:addnew = 0
actions:addexisting = 0
------------------------------

So what I am looking to do is, display all records which match the current childs PID. (ParentID), including this child. I merely copied the above code for Children, and pasted it, commenting out the CID info, since I do not want to restrict the records to just this Child. Right now I am without an Idea. Tonight I will try your suggestion to see a screenshot. A + - Tree would be nice..but I dont know how complicated that is at this stage.

Thank you kindly.

[Aoirthoir]

Ok looked over your code again. Regarding the PageID and the ParentID fields, are these in the same table? Currently I am using the R_ table to relate the table to itself in a multi to multi relationship. Can this be used in that case?

Thanks

[njw]

Have you looked at dotProject - open source project management software - you can just load it onto your system. It'll do most of what you're trying to do. Mind you, if you're enjoying the intellectual exercise, then don't let me stop you ...

[Aoirthoir]

Yes Sir. In fact that was one of the first ones I downloaded. However to even get started you have to fill in all sorts of parameters. Hours open, hours worked, etc. It is geared towards billing as well. Whereas all I need is a task list showing related tasks.

However, the mental exercise is good also, because if the tree thing can get working, I wouldnt use tabs for a lot of things. Maybe the biggies, like Patients, Insurance Companies, Doctors, Vendors and so forth. But then when you click on one of those, and you get to a particular vendor/patient whatever, a plus/minus tree system appears. Say with Links for Notes, Orders, and so on. Click the plus by orders and a list of his orders shows up.

But even more importantly than the tree thing..if I am on a patient I need to see his Insurance Companies (they might have several) So that is a tab. Then if I am on an insurance company I need to see their patients. So that is also a tab. Lastly if I want to see all patients that share this patients insurance comany, I want to click a tab...thus the sibling link. But there is some issues figuring it out because it is a many to many link. Which means the sibling thing wont know WHICH insurance company to display.

[njw]

It was a tongue in cheek comment ...

Perhaps a different solution would be the ability to have sub tabs - easy to do in Access - so you keep the main record data in the top half of the screen and have related data tabs below. That probably doesn't make much sense without a picture, but I think the concept was discussed a while ago. Wouldn't solve all the problems either!

I am sure there's a task manageemnt package around somewhere - I've looked at a lot of different software recently and my brain is no longer as good at recall as it once was ... Watch this space, but don't hold your breath!

[Aoirthoir]

Actually Mr. NJW, I really appreciate all of your suggestions. You've helped me a lot thinking through some of these things. As to the way it is done in Access, I've used access before so I know what you mean. It is also very similar to Act!

I like the Idea and we've discussed variations of it on the forums before. As we all learn more about the dataface Smarty Templates, we will be more able to assist Mr. Steve in implementing some of these ideas. Right now he is the sole developer and that puts a huge load on his shoulders.

In the meantime, I am going to kick it hard on the docs this weekend. I would like your help if you are still interested and Mr. Dirk's as well. With three of us working together we can maybe get something done. (As to the timeframe, I can work this weekend but I understand if yall dont want to and would rather start sometime later.)