combining tables into one list

[hwachai]

i m not sure this is the right section i ask this question....

 

my current database:-

 

---------------------------------------------------------------------------

projects A ----- task table A ( task title, user, status) 

                         task table B ( task title, user, status)

                         task table C ( task title, user, status)

projects B ----- task table A ( task title, user, status) 

                         task table B ( task title, user, status)

                         task table C ( task title, user, status)

----------------------------------------------------------------------------

I have main projects table, each project create different task table base on current project id. And each task table have its own task list....

now my problem is to create a report showing the project with all the task.... like combining the task table into 1 list. What should i do to combine all my task a, b, and c?

--------------------------------------------------------------------------

project A

title   l   user    l   status  (sort by status)

project B

title   l   user    l   status (sort by status)

 

 

 

I m new on using filemaker pro 12. I recontructing the database input for my department next year, to replace current database structure that done by previous manager... really appreciate your help.

 

thanks

hwachai

 

[qube99]

I think I might have bult only a single task table and designated A, B and C in a field, like a category. That makes it pretty simple to view them collectively or individually. I might go further and do the same for Projects so that there is a single table for everything. Then use various layouts with filtered portals to display just the parts I want to see. A data structure like this will also open up your possibilities for creating subsummaries.

 

You might enjoy the FileMaker Training Series. I think its a free download (sorry dont have the link) and is pretty good for learning these sorts of things.

[hwachai]

Hi, i m sorry for didn't explain cleary on my situation...

Maybe I explain further on my database, each task table have their individual field and drop down menu.

 

projects A -----  task table A ( task title - drop down menu, user, status, material, size) 

                           task table B ( task title - drop down menu , user, status, contact, function)

                           task table C ( task title - drop down menu, user, status, layout )

                           task table D ( task title - drop down menu, user, status, duration, video size )

 

projects B -----  task table A ( task title - drop down menu, user, status, material, size) 

                           task table B ( task title - drop down menu, user, status)

                           task table C ( task title - drop down menu, user, status, layout )

                           task table D ( task title - drop down menu, user, status, duration, video size )

 

The reason to separate all my table because, if I only create one record with so many details will be quite confusing.... I have total 9 task tables, each table have more than 15 tasks.

 

Now my people need one report to keep track all the tasks for each project like below : -

 

project A

            title          l      user       l        status  (sort by status)      <---- i only need this 3 field

-      Task AAA           Jimmy               in progress       (from table task A)

-      Task BBB           Jimmy               in progress       (from table task A)

-      Task CCC           Henry               in progress      (from table task

-      Task DDD           Sandy              in progress       (from table task

-      Task EEE           Victor               in progress       (from table task C)

-      Task AAA           Sam                 in progress       (from table task C)

-      Task BBB           Victor               in progress       (from table task C)

-      Task CCC           Siva                in progress       (from table task D)

-      Task DDD           Tony               in progress       (from table task D)

-      Task EEE           Jimmy               in progress      (from table task D)

 

project B

-      Task AAA           Jimmy               in progress       (from table task A)

-      Task BBB           Jimmy               in progress       (from table task A)

-      Task CCC           Henry               in progress      (from table task

-      Task DDD           Sandy              in progress       (from table task

-      Task EEE           Victor               in progress       (from table task C)

-      Task AAA           Sam                 in progress       (from table task C)

-      Task BBB           Victor               in progress       (from table task C)

-      Task CCC           Siva                in progress       (from table task D)

-      Task DDD           Tony               in progress       (from table task D)

-      Task EEE           Jimmy               in progress      (from table task D)

 

( click on the task title with direct to task detail )

Please, need help on this. Combine the info into one table? get value from each table? or I create another task table call "All task",

every single task created will create another copy in "All task"?

 

Thanks!!!

[LaRetta]

Regardless, Steve's recommendation is sound.  Your life will be much simpler if you merge them and use Category or Type or other fields to identify their differences.  Then relationships, finds, and reports filter according to those fields.

[hwachai]

maybe you are right.... I should try to build them in one table. Before I redo my database, here is my screen cap sample on my project form... don't worry about the details, is just dummy at the moment....

 

 

as you see, GFX, DTP, VIZ, VFX, LED, MISC is my table task (categories), all have different requirement and unique interface/form. When the project is created, I will cascade the job for different departments, each job have different task for different people, and each department will have their own report.

The only common filed is title, status, user and delivery date. When I click on new task, it will create a task for that particular Categories.

 

If I want make them in one big table, how can I make it into different form's format for each categories? Hope someone can enlighten me on this....

 

Actually I thought is easy to combine the common fields from each table into one report.

[LaRetta]

The issue is ... it isn't easy to combine them into one report.  And you can't place a portal on your User's layout so they can see their tasks - you will need several.  Unfortunately, you will hit roadblocks everywhere you turn. 

 

In today's world of WAN and mobile, skinny tables are recommended but they should be split so the same fields are in the single primary table and some of the fields split off to a one-to-one related table.  But 15 fields is not much of burden so a split wouldn't (necessarily) be required.

 

Can you provide screen shots of these Tasks tables so I can see all the fields in them?  I ask because a major mistake folks can make is, for example in a questionnaire, putting the questions as the field names.  This seems logical but it is disaster. 

 

The reason to separate all my table because, if I only create one record with so many details will be quite confusing.... I have total 9 task tables, each table have more than 15 tasks.

 

9 Tasks tables with 15 tasks each sounds like you are moving off base.  Truly, the beginning structure is the most critical and now is the time to get it right; once you are further down the road and realise it, it will be major undertaking.  

 

If I want make them in one big table, how can I make it into different form's format for each categories? Hope someone can enlighten me on this....

 

We do not have enough information yet to advise how to display.  It is easy to perform a find for the category and switch to layout with fields only from that category and script trigger (OnRecordLoad) can switch to display the correct form if scrolling record-set of all tasks.  I think you need:

 

Projects

ProjectID (unique auto-enter serial )

-- one each unique Project

 

Tasks

TaskID (unique auto-enter serial )

-- one each of a possible task for all projects

 

AssignedTasks

AssignedID (unique auto-enter serial)

-- one each of every assignment

ProjectID ... assigned during data entry

TaskID ... assigned during data entry

Status

 

I realize I gave you quite a bit but this business isn't as easy as it looks (not to do it right) regardless of what FileMaker tells people.  Creating files is simple; knowing how to create them isn't.  Once we see screen shots of your tasks tables we can advise further.  We are willing to help you get the right start.

[qube99]

It looks to me like he is already using portals on a tabbed layout. So let's think about another way to create a list of all the tasks.

 

You have 6 task tables that are related to a single record. Those 6 tables are displayed in portals. Maybe a special table could simply be related to the other 9 so that when a new record was created somewhere, a copy is created in the special table with a little scripting. In this manner he can have the single table schema for reports, subsummaries, charts, etc without altering the multi-table scheme he is probably employing.

 

Seeing those portals tells me his schema is more sophisticated than I originally thought. Can we get a peek at your ERD?

 

If I were designing this from scratch I'd use a single task table for the whole project. It would have the same layout as now but would link to the table with the various methods LaRetta describes. It would not matter if that table had 150 or 200 or 500 fields in it since you will only display a few of them at a time.

[LaRetta]

 It would not matter if that table had 150 or 200 or 500 fields in it since you will only display a few of them at a time.

 

Well, no, that is not the direction we need to go. It matters a great deal for two reasons, 1) when a record is served up (downloaded from the server), it downloads ALL fields for the record except container and it does not matter if the fields are displayed or hidden or non-existent on the layout.  So the more fields in a table the slower it will load and 2) if there are 500 fields (or even 100 fields) then it points EXACTLY to my concern ... that fields are (probably) used when they should be records.  So if you have a 500-record table but only put one field on the layout, 500 fields download for that single record (and if in form view) 25 more records (with their 500 fields) are pre-fetched as well.

 

It looks to me like he is already using portals on a tabbed layout. So let's think about another way to create a list of all the tasks. 

 

What you are describing is really a super table and the basis for what I described by having only primary 'like' fields in the main table.  It is used more and more to control the display (and thus the download) of served records for precisely point 1 above.  This is the table which is used for displaying lists etc. because it is thin.

 

Maybe a special table could simply be related to the other 9 so that when a new record was created somewhere, a copy is created in the special table with a little scripting.

 

There will be no need to complicate the issue by scripting when, if designed properly, the 1:1 auxiliary fields will handle it nicely - you simply cross place the fields and turn on 'Allow Creation' in the relationship.  But as we both realise, we lack information to provide good answer and hwachai now knows what we need to know to continue the discussion.  :-)

[hwachai]

wow, hmmm..... i cant print screen for you at the moment, they are in my office. i can use text to show you.

yes, i m already using the portals on each tab. I have main 4 user will create the record (project),assign to job for the rest of my staff, but they are onli able to view the details based on the categories ( gfx, vfx, led, misc)

 

gfx

title (drop down menu : aaaaa, bbbbb, ccccc, dddddd, eeeeee)

delivery date

tx date

editing date

allocated day

status (drop down menu : in progress, completed, over due, canceled)

duration (by second)

style ( drop down menu : relief, sunken, engrave)

user ( drop down name list)

frame size

quantity:

modified name and date

created name and date

additional information

 

viz

title (drop down menu : full frame, bug, templates, set, back)

delivery date:

live date:

location :

allocated days:

status (drop down menu : in progress, completed, over due, canceled)

details : (drop down menu : light, medium, complicated)

production manager :

quantity :

user ( drop down name list)

platform : (dropdown menu : win, mac, unix)

modified name and date

created name and date

additional information

 

LED

title (drop down menu : opening, songs, generic, ending)

delivery date

tx date

allocated days:

vendor : (drop down menu : x company, y company, zmcompany)

status (drop down menu : in progress, completed, over due, canceled)

duration by minutes

element : (check box selection : video , photo, title, lyric)

user ( drop down name list)

projector size :

quantity :

lighting manager :

modified name and date

created name and date

additional information

 

this is my task detail, for different departments. But the cost is from one main project. Each categories have their own rate, cost and budget.

 

my next step, i need to create a job sheet that need my people fill in the working hours based on their task. Like check in check out time.... It will be another table relate with the project Id, and task id.

 

So the best solution for me is to redo the whole thing, make it into one table? i m afraid it will too confusing if too much info in one form

 

thanks for all your reply and help. i m not stuck here forever....

[LaRetta]

You are not in as bad a shape as I thought.  YAY!  You do not have that many unique fields, looking at the list - only 16 not in blue.  And you are not writing tasks into the field names - good on you.  Moreover, some of the fields could be multi-purposed (Manager, Duration, Size).  So a standard relational structure as I outlined above would serve you nicely and these Tasks tables would become a single AssignedTasks table.

 

gfx

title (drop down menu : aaaaa, bbbbb, ccccc, dddddd, eeeeee)

delivery date

tx date

editing date

allocated day

status (drop down menu : in progress, completed, over due, canceled)

duration (by second)

style ( drop down menu : relief, sunken, engrave)

user ( drop down name list)

frame size

quantity:

modified name and date

created name and date

additional information

 

viz

title (drop down menu : full frame, bug, templates, set, back)

delivery date:

live date:

location :

allocated days:

status (drop down menu : in progress, completed, over due, canceled)

details : (drop down menu : light, medium, complicated)

production manager :

quantity :

user ( drop down name list)

platform : (dropdown menu : win, mac, unix)

modified name and date

created name and date

additional information

 

LED

title (drop down menu : opening, songs, generic, ending)

delivery date

tx date

allocated days:

vendor : (drop down menu : x company, y company, zmcompany)

status (drop down menu : in progress, completed, over due, canceled)

duration by minutes

element : (check box selection : video , photo, title, lyric)

user ( drop down name list)

projector size :

quantity :

lighting manager :

modified name and date

created name and date

additional information

 

So create a Category field then find the tasks from the first Category and import it, run 'Replace Field Contents' down the found set to fill their Category then repeat with next set of tables.

 

As it was, with 9 tables, you would have needed 9 summary fields ... nine of each calculation ... all identical but in different tables.  With the single table, you will have one of each calc because they will evaluate based upon your found set ( of Categories ).  And you can now produce all the reports you may need.  

 

You do not have enough unique fields here to warrant a super table of AssignedTasks.  

 

Edited:  I corrected the sentence in red to make it more clear.

Also, I do not see primary keys in your example.  It is CRITICAL that you use primary and foreign keys in all your tables and that your relationships are based upon those auto-enter serial keys.

Edited December 19, 2013 by LaRetta

[hwachai]

cool!!! ... Thanks for the input... I m decided to redo the things again. Put them in one table, and generate 9 summary fields.

 

To minimize the risk on mistake when keying the record, I plan to use bullet point selection...... when you click and adding new task on gfx, DTO or others, the interface will change accordingly.

 

edit : primary keys?....  currently i m using auto serial keys for my each table, and sub table have the serial from project table and its own serial (auto)... I m not quite sure what is primary or foreign key... :/

 

 

Thanks!

 

hwachai