Complex Portal Problem

[SoCalMacDude]

I need to show a daily schedule of resources on a layout.

I have a Schedule table, where each record is a unique date.

Then I have a Job Ticket table where each record has a unique job ticket number, related to above by date.

I have a Schedule Line Items table, where records are related by a calculation of Job# and Date.

Then I have a Truck Line Item table, where each record is a unique truck that the warehouse owns. Same with Trailers, Other Equipment, and Crew.

In portals on the Job Ticket layout, they choose trucks, trailers, crew, etc., needed to complete the job, which is done on a particular date. Everything is related by the Job#Date field.

So, I need a layout that is for a single day, showing each job for that day, and within each job, each resource line item required for the job.

Been trying all combinations that I thought should work, but can't make it show the right records.

After beating my head against this wall, I decided to post! AHHHH!!!

[Ohgo_Ohgo]

Please post a sample file ;-)

If You can just make a copy, leave some sample data, save a compacted copy and Drop Zip it,

should be so much faster to help You or at least understand better !

[SoCalMacDude]

Thanks. I think I nailed it, but I will upload today if I need help/

Thanks!

[SoCalMacDude]

Yep, I definately need HELP!

Here is a sample of the file. This file was created by the customer, and I am adding functionality to it. The main goal is described in the first post.

From Job Tickets layout, click Schedule. Schedule is where I want to show a single date, and all of the jobs scheduled for that date. I also want to show the crew and trucks for that date.

A problem that I just found today, that was OK before, is that I can no longer add crew members (or trucks, etc) to the crew line items (etc.) portal on the blue Crew tab (scroll right, schedule!)

Any help is much appreciated - preferably today! I'm at the customer!! :-))

Login for file is: Admin

Password: sets

Job_Tickets.fp7.zip

[SoCalMacDude]

Can someone take a look at my sample database & see if you can figure out why the CREW portal cannot create a record?

Scroll right to see the Schedule tab, then click Crew. As you can see, I was able to create portal records (cause some have line items) but now it says "Field not modifiable."

What happened??

User: Admin

Password: set

[Lee Smith]

The password and id do not work for me.

Lee

[SoCalMacDude]

Thanks for the reply, Lee.

I set the password yesterday when I posted that post that you deleted, and now I can't come up with the PW I used! It had to do with movie sets, which is what the customer does.

Any chance you could find that deleted post? I need it now, too!

Thanks.

[Lee Smith]

I'm not sure that the attachments for deleted Duplicate Posts are kept. I will do a search and see what I can come up with.

Lee

[Lee Smith]

Is this the correct file name?

BD_job_tracking_test_v3Clone.fp7

Lee

[Ohgo_Ohgo]

Login for file is: Admin

Password: sets

This one works for me !!

A problem that I just found today, that was OK before, is that I can no longer add crew members (or trucks, etc) to the crew line items (etc.) portal on the blue Crew tab (scroll right, schedule!)

Look’s ok to me I can add records on the “Scheduled Items” Tab in the blue “Crew” Tab

I can also add records in Your “schedule” button go to “Schedule Line Item Test” layout.

But I think that's wrong any way. You don't realise this records easy because You have alpha sort on the Name so the new record “disappears” in the list according the sort. Also You don't have the time field there so the new crew has no work time assigned.

To allow entry's in that Daily Schedule view is wrong in my point of view You should do that only in Your Job Ticket Layout.

Also I think that the Schedule TOC is a more promising “start” to solve Your Problem.

May I give You some advice (without being to “pushy” ;-) )

1. please clean up Your Relation ship graph. It’s actually not so wired as it looks first.

2. If someone else started this program , better don’t touch “his” relationship graph. Any change could easily break some thing You are not aware of.

You easily just start a new separate “island” of “new” relationships may be with a global table in the centre. You are In full control about what You need and all toc’s and You can Play & test as much You need without any influence to the “old” rest of the program.

good look SoCalMacDude !

[SoCalMacDude]

Thanks Ohgo_Ohgo,

I get it! I removed a critical field, I think.

I hate to admit it, but I am the one who created that kluge of relationships! Before me, it was a flat file.

I have made myself a bit confused, and have spent hours trying to sort it out, that's why I asked for help, so I am grateful for your input. This is my first 8.5 job, and I need to get acclimated to it!

Would you be so kind as to clean the chart up a bit, so I can see how it's done right? I tried to make it make sense, but it got complicated real fast!

I understand what you say about inputting only on the Job Ticket layout, but the cust insisted he wanted to be able to input directly on the schedule, so that he can scroll to see equipment utilization, sort them. etc.

As my first post stated, I need to see all of this stuff on a schedule layout, and showing only one date at a time.

Thanks for your help!

Ken

[SoCalMacDude]

Thanks for checking, Lee.

Here is the file (maybe you can shed some light!)

Username: Admin

Password: sets

Also, could you move the 3 private messages between myself & Ohgo_Ohgo to the main forum as he requested? It may provide others more information, hopefully I can get more help!

Thanks,

Ken

Job_Tickets.fp7.zip

[Lee Smith]

I don't have access to Your Private Topic (hence the word Private). However, one of you two who do have access to this group of messages can do this instead.

Lee

[SoCalMacDude]

Sorry, I thought you were a moderator with special access! -)

I couldn't see how to move the topics.

Did you have a look at the database? Any solutions?

Thanks,

SoCalMacDude

[Lee Smith]

I'm a Moderator with extra duties,

I don't think even the owners have that particular privilage.

No, I haven't looked at your file, I'm waiting to to see what was discussed in the Private Topic so that I don't waste time going over the same ground.

Lee

[Ohgo_Ohgo]

Also, could you move the 3 private messages between myself & Ohgo_Ohgo to the main forum

Hi SoCalMacDude!

I think we don't need to move the 3 private messages - no real important stuff to the problem.

I am very relieved that actually You have programmed the whole file so far, shows me that You can do quite well in FMP 8 already ! ;-)

I had this moments of “confusion” about relations and the graph when I moved from FMP 6 to FMP 7 quite regularly ;-).

Just a question can you move “away” from Your customer eyes and work in private ? To get some stress away from You ?

In the meantime I will try to “dive” a little more in Your file, but please understand I am on the other side of the world ( time delay ) and have only little FM time beside my real job.

One more can You get Yourself FMP 8.5 ADVANCED - the DEBUG mode always helped me a lot when my head was spinning ;-)

As You wished I will post a little cleaned up Relation ship Graph in a short time.

rgds from the Island of the Goods

[Ohgo_Ohgo]

Did you have a look at the database? Any solutions?

Yes '-)

and I would first suggest that all you have in "Schedule Line Items" - this is only something like the header of the line items can easily go into Your Job ticket table.

And that all Your real Line items ( crew, truck, trailer,other) go into only one table and just are tagged with e.g. a letter like Cr,Tu,Tr.Ot.

Also all Your tables Crew ,Truck,Trailer,Other can be only one table with same tagging as above.

and Your job#Date field is not necessary because in FMP 7 upward You can make relations with more than one connection easily. e.g.

Job# = Job#

Date = Date

same effect than Your “extra” job#Date field

also then in Your real lineItem table if You what to see only Crew You make a relation like

JobTicket to lineItem

Job# = Job# ( no date Your job# is unique any way !!?? )

marker for Crew “Cr” = tagging line item

This will be quite a change to make but I think it will be worth in the long run to keep You application slim and understandable.

You original “problem” How can I see all Lineitems of a special day ??

well just use a global with that date connected to the line Items.

If You need Cr. Truck, Trailer, Other separate well make relations like

Date = Date

Marker (Cr ) = line item tagged ( Cr)

SoCalMacDude , I don't know if my english makes sense, may be You can also look the web from “Multi Key relation ships” , and Tagging of records

regards and waiting Your consideration

may be look at

http://www.digfm.org/ref/FM7_key_concepts.pdf

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=609&mode=thread&order=0&thold=0

BTW http://www.filemakermagazine.com helped me a lot to know what I know today but it’s a paid subscription (realy worth the money ;-) )

AND PLEASE ANYBODY WHO CAN EXPLAIN BETTER OR HAS A FASTER SOLUTION PLEASE JUMP INTO OUR CONVERSATION ! YOU ARE WELCOME !

[SoCalMacDude]

Since I can't find a way to move it, I will copy and paste!

My Post:

Hi,

Thanks for replying to my post!

I posted a sample database to show the problem.

My first concern is that I cannot create new portal records from the "crew" tab (under Schedule tab, scroll right) on Job Ticket layout. It worked before, but I don't know when it quit, therefore what I did to mess it up! It looks set up correctly to me, but that's probably why it doesn't work!!

Could you take a look?

http://www.fmforums.com/forum/showtopic.php?tid/18...

Thanks,

Ken

His Response:

Hi SoCalMacDude !

I lookes at Your file and gave some "advice' sorry if too direct ! ;-)

but may be wrong I posted on the normal forum

Take care and pls really try to make a new seperate ( not coneccted with the old ) relation ship graph.

took me a while to find that "trick' and I use it now always in my own programming.

every "special" layout has its separate RSG , helped me a lot and seams not to be any draw back.

Ohgo_Ohgo

My Reply:

Hi Ohgo_Ohgo,

I LOVED your reply!! Not too direct at all!

I will be working on this at the customer's site today.

I don't understand a couple of your suggestions. Maybe you could clearify . . .

What do you mead by "a global table in the middle?"

Also, I am confused about what table the layout should be associated with, in order to see the listing of jobs for the day. Is there any way to have it automatically show just the jobs for that day automatically, without actually triggering a search from a script? If so, how would you do it in this solution?

If it is easier to show, please make the changes to the sample file & post it! (Maybe we should move this to the main forum!)

Thanks! -))

Since I created every relationship in the file, I would rather locate the problem and fix it. There is not really that much going on, that I would fear braking something else. This is what I need to make work!

I am still a bit confused about which table should be used as the "home" layout in order to see all line items for a given day in a list view, and be able to change them from that layout. Also, on the job ticket layout, I can't figure out why the crew portal will no longer create a Crew line item. I looked at it, and I did not delete any fields since it was in working order.

Any help?

Thanks,

Ken

[SoCalMacDude]

Hi Ohgo_Ohgo!

You're looking quite handsome tonight!

I just posted, refreshed, & you had also just posted! What a coincidence!

I'm about ready to hit the sack tonight, just thought I'd check the boards.

Looks like you have some good content, so I will check it out!

Take care,

Ken

[SoCalMacDude]

Yes, I have a few days to breathe now, although I think the only file I have is our sample. But that's fine, I just need to get it functioning!

Thanks sooooo much for the help!

Good point, maybe I don't need the Schedule Line Items table at all. Each job ticket is only good for one activity date.

The reason I have the crew, trucks, trailers, and Other Equipment is because some jobs have few of these resources, and some have many. I wanted flexibility, but my layout ended up being 4 portals, each with a finite number of items. Maybe a repeating field would work just fine.

Other tables, probably also could be consolidated.

I tried the Job# & Date as 2 criteria, but it didn't seem to work, hence the brilliant solution!! ;-)) I'll try that one again, because I'm sure you are correct!

I know why I made the Job# AND date - because sometimes they will load a truck one evening for delivery at 4:00 am the next morning. They need the same job # on two schedule days.

Excellent suggestions! I will try to find time to work on this before Tuesday, but I must work on the video project I am doing for my daughter's HS Cross Country team!! Lots of time needed there before the banquet!!

Thank you for your help.

Ken

[Ohgo_Ohgo]

Maybe a repeating field would work just fine.

No need so far !

I know why I made the Job# AND date - because sometimes they will load a truck one evening for delivery at 4:00 am the next morning. They need the same job # on two schedule days.

Well than don't look at the day = date, look only at the unique job # . Look is a relationship !.

Relationship means You are looking from one table occurrence ( layout) to an other TOC. And than what are You seeing ? the records which are "limited” by what key ?

[SoCalMacDude]

OK, I am taking your advice, and making crew, trucks, etc. repeating fields in the Job Ticket database.

Now, I want to look at a day, and see the schedule for that day. I want to show it through a relationship, and not have to click a button to "add it to the schedule."

So, the Schedule table is dates, one record for each day, the Job Tickets table is one record for each job ticket. Do I need a line items table at all?

With a Date = Date relationship between the two tables, what layout do I create (from which table perspective) to show a schedule of the day's job tickets, and how do I show only those job tickets for that day? Also, how do I change days (fields in headers don't always wrok).

Anyone?

[Ohgo_Ohgo]

OK, I am taking your advice, and making crew, trucks, etc. repeating fields in the Job Ticket database.

Sorry that's not what I suggested.

I advised to put it all in one table and tag it according to its use/meaning.

It would than be a simple relation date = date to show them all in Your schedule layout or You could separate them in different portals by the additional

tag =tag relation ship

( please also update Your avatar showing You are using FMP 8.5 now. Otherwise my suggestions are for sure wrong - thanks )