Finding tasks that are due in range of a week

[nburghardt]

I know this should be rather easy, but I am blanking on this. I want each person to be able to pull up, either by script or a calculated field, all the tasks due for a week beginning every Saturday and ending every Friday of the year. Should I do a script, have my weeks of the year in a lookup so I only have to change the lookup every year? I was thinking of using a script with something like this If...then statement:

If(task1 is equal to or greater than 11/5/05 and is equal to or less than 11/11/05, task1, If task2 is equal to or greater than 11/12/05 and is equal to or less than 11/18/05, task2.....through every task and/or week of the year??? That is where it becomes mind boggling. I would certainly appreciate any help on the best way to do this.

Edited November 6, 2005 by Guest

[LaRetta]

Hi nburghardt,

You need to think in SPANS; whether by multikey relationship or Find. Here are two approaches. You can tell me what sounds about right and we can go from there. How do you want the resultant data viewed? Is Tasks your main file or will you be viewing this from another related file?

A SEARCH: The simplest would be allowing your Users to perform a Find. And no matter WHAT date they put in, system returns that date's weekly tasks. For example, Status(CurrentDate) - DayOfWeek(Status(CurrentDate)) will always give you the prior Saturday. So if they type 11/24 into global date called gWeek (wanting to see their Tasks for Thanksgiving week), resulting script-step of Insert Calculated Result will give you the span for search:

gWeek - DayOfWeek(gWeek) & "..." & gWeek - DayOfWeek(gWeek) + 6

A RELATIONSHIP: You can create a multikey date on the left to your Task date on the right (according to a span from Saturday through Friday). This could be joining the same file (Tasks) or from another file to tasks. You could provide global filter which allows a User to specify date, and a portal could prefill will the resulting tasks. To create a multikey numeric date span, you would create text calculation of:

(gWeek - DayOfWeek(gWeek)) & "¶" &

(gWeek - DayOfWeek(gWeek)+ 1) & "¶" &

(gWeek - DayOfWeek(gWeek)+ 2) & "¶" &

(gWeek - DayOfWeek(gWeek)+ 3) & "¶" &

(gWeek - DayOfWeek(gWeek)+ 4) & "¶" &

(gWeek - DayOfWeek(gWeek)+ 5) & "¶" &

(gWeek - DayOfWeek(gWeek)+ 6)

... joining this left number-date key to a Tasks date calc set as number. You may have to deal with typecasting here. It's been awhile since I'm worked in vs. 6 and my 6 version is acting wonky so I can't test it. You would need to include (I assume) only 'Active' or 'Still Due' tasks and maybe a User Name? More information is necessary before I give you a specific solution.

UPDATE: Wow. Working in 6 again reminds me of how easy things are in 7!!

LaRetta

[nburghardt]

Thank you so much for your prompt response.

As far as the structure of the database goes, the database is for an advertising agency and they need to be able to forecast how long it will take to produce ads. They need a different schedule for Ads, Web Design, Radio or TV.

The main database has 33 task fields that make up different schedules that are imported from related files depending on the type of schedule they need. The 33 task fields are not all used in each schedule. The traffic people(managers of the schedules) choose the schedule they need by pushing a button and a "canned schedule" is imported. The traffic person then fills in a start date and the dates fill in automatically, depending on how many days each task takes which has already been predefined. That person can then see when the delivery date will be and the dates adjust for weekends and holidays. I made scripts that enabled each person to find the tasks that are "due today" or "past due" which showed the traffic person's name, and only found the active jobs and the "status description" field of the tasks that were due or past due.

That worked fine except now they want to be able to see on Monday morning what tasks are due for the coming week. That is where I am now I need to find a week's range of tasks.

My calculation was 33 if...then statements.Here it is: Status description calulation:If(status33="Past Due", Task Descrip 33, If(status32="Past Due", Task Descrip 32, If(status31="Past Due", Task Descrip 31, If(status30="Past Due", Task Descrip 30, If(status29="Past Due", Task Descrip 29, If(status28="Past Due", Task Descrip 28, If(status27="Past Due", Task Descrip 27, If(status26="Past Due", Task Descrip 26, If(status25="Past Due", Task Descrip 25, If(status24="Past Due", Task Descrip 24, If(status23="Past Due", Task Descrip 23, If(status22="Past Due", Task Descrip 22, If(status21="Past Due", Task Descrip 21, If(status20="Past Due", Task Descrip 20, If(status19="Past Due", Task Descrip 19, If(status18="Past Due", Task Descrip 18, If(status17="Past Due", Task Descrip 17, If(status16="Past Due", Task Descrip 16, If(status15="Past Due", Task Descrip 15, If(status14="Past Due", Task Descrip 14, If(status13="Past Due", Task Descrip 13, If(status12="Past Due", Task Descrip 12, If(status11="Past Due", Task Descrip 11, If(status10="Past Due", Task Descrip 10, If(status9="Past Due", Task Descrip 9, If(status8="Past Due", Task Descrip 8, If(status7="Past Due", Task Descrip 7, If(status6="Past Due", Task Descrip 6, If(status5="past due",Task Descrip 5, If(status4="past due", Task Descrip 4, If(status3="past due", Task Descrip 3, If(status2="past due",Task Descrip 2, If(status1="past due", Task Descrip 1,""))))))))))))))))))))))))))))))))) It would look at each taskdate and if past due would look at the next field etc until it came to a past due date. Then it would display the description of that particular task.

I had created a look-up file with 52 weeks, thinking I could do a portal and find on "week 1, week 2, etc. but don't know how to implement.

I am sorry but didn't quite understand your suggestion about gWeek. Would you have 52 Global week fields? One for each week of the year? Such as gweek1, gweek2, etc.

I created a global field called gWeek. But I can't seem to do any finds with it and my script Insert Calculated Result does not work.

I thought I was intermediate, but I see I am more of a novice. I hope you can help.

[LaRetta]

Hi nburghardt,

Where do I start? I guess at the crux. You have created multiple fields which should have been related records. I can simplify your calculation by making it a Case() statement instead. This is not the problem you posted about, but it will be faster:

Case(

status33="Past Due", Task Descrip 33,

status32="Past Due", Task Descrip 32,

status31="Past Due", Task Descrip 31,

status30="Past Due", Task Descrip 30,

status29="Past Due", Task Descrip 29,

status28="Past Due", Task Descrip 28,

status27="Past Due", Task Descrip 27,

status26="Past Due", Task Descrip 26,

status25="Past Due", Task Descrip 25,

status24="Past Due", Task Descrip 24,

status23="Past Due", Task Descrip 23,

status22="Past Due", Task Descrip 22,

status21="Past Due", Task Descrip 21,

status20="Past Due", Task Descrip 20,

status19="Past Due", Task Descrip 19,

status18="Past Due", Task Descrip 18,

status17="Past Due", Task Descrip 17,

status16="Past Due", Task Descrip 16,

status15="Past Due", Task Descrip 15,

status14="Past Due", Task Descrip 14,

status13="Past Due", Task Descrip 13,

status12="Past Due", Task Descrip 12,

status11="Past Due", Task Descrip 11,

status10="Past Due", Task Descrip 10,

status9="Past Due", Task Descrip 9,

status8="Past Due", Task Descrip 8,

status7="Past Due", Task Descrip 7,

status6="Past Due", Task Descrip 6,

status5="past due",Task Descrip 5,

status4="past due", Task Descrip 4,

status3="past due", Task Descrip 3,

status2="past due",Task Descrip 2,

status1="past due", Task Descrip 1)

... but as for providing a solution to your current problem, I make no guarantees. If you post your file, I promise to take a look. There are possibly some shortcuts I can take (I'm good at cheating and shortcuts). That's the best I can offer. YOu will need to perform a Find in multiple fields, I believe. Not sure we can use portal with your solution - I can't envision what you have. I highly suggest you consider a rewrite to a relational structure as soon as you can manage it. If you stay with this structure, you will constantly hit these same types of walls. Incorrect structure takes 10 times as long to create - and 100 times as long to modify in any way (as you are finding out).

I will be here if you want me to try for you.

LaRetta

[nburghardt]

Thank you again for your response and willingness to help.

I am enclosing my files and am very receptive to any suggestions you are willing to make.

(The colors were dictated....not my choice at all.)

Nancy2.zip

[LaRetta]

Is this solution currently being used? Or is it still in the production phase?

[LaRetta]

Well, I reinstalled vs. 6 so I could open it. But the only dates I can find in your file is TaskDate (many fields). But all of those dates are like: 4/26/0001??? I'm afraid I have no idea what I'm looking at or how your solution works together.

Sorry I couldn't help; I surely wanted to. If you can, rethink the structure now before you get any deeper into this. I think you need to script multiple find requests - one for each date field (a valid date field). But TaskDate won't work. Do you have real dates somewhere I can't see?

Maybe others can spot a quick-fix solution for you.

LaRetta

[Søren Dyhr]

Maybe others can spot a quick-fix solution for you.

If they can, should they have thier arm pulled off and being beaten with the juicy end of it!!! The solution is inadquately structured, and is merely a collection of redundant calc's and fields ...which only can remedied by a new ER and a rewrite, which will take some time to accomplish.

Solutions like this one are once and a while uploaded, totally ignoring that methodology to structure data actually exists. Eagerly hoping that someone is AUTISTIC enough to spot the needle in the haystack and save the day with a fix.

Since you're on 6 will your developement benefit from learning the first 5 chapters of this book:

http://www.amazon.com/exec/obidos/tg/detail/-/1556228597/ref=cm_aya_asin.title/102-8224836-5531347?%5Fencoding=UTF8&v=glance

...the following chapters aren't bad reading either!

--sd

[nburghardt]

Hi,

It is being tested by the traffic manager only. So it really is still in production

[nburghardt]

Hi LaRetta,

Thanks again for working with me. When you open "_Traffic Database", the opening page displays buttons. If you click Ad/O/C, it will import a schedule and take you to the Ad/O/C/ data entry layout.

You then choose the client, click on a job# in the portal and the job# and job description fills in. The people on the task are filled in next.

But the most crucial field is the Start Date. Once that date is filled in, all the date fields will fill in.

Do you have any ideas how I should restructure this database? I would appreciate any suggestions, you could give me. Thank you again for your time and effort.

I feel this has been an incredible resource and your help specifically. I really want to learn everything I can from this forum. It has been a humbling experience.

[nburghardt]

Thank you for the book suggestion. I will order it immediately!

[LaRetta]

Hi Nancy,

I'm quite pressed for time right now but if you can give me a few days, I'll slide this puppy into my schedule. You posses a hungry spirit and an open mind - I'm a softie for both.

We'll establish a solid foundation upon which to build your solution. Then, by studing books, reading Forums and asking questions here when you get stuck, you'll be well on your way. It may seem that starting over will take longer than correcting what you have but actually the reverse is true. A good structure will allow you to design 50 times faster!!

I will send you a private message. I have some 'homework' of my own to suggest ... and none of it involves touching FileMaker yet.

LaRetta

[Søren Dyhr]

I'll give the establishment of a structure a stab from what I can guess the intentions are in your file!!

--sd

[LaRetta]

I thought you were attaching a file, Soren! No files? Maybe I mis-read you (again)?

We're defining the process on paper first (the walk-through) and then creating base files (client, staff, notes, holidays, tasks etc). I think it would take longer to delete the unnecessary fields from each file than to create base fields in new files. And Canned TV, Canned Radio files etc will be combined (I believe) - it depends upon what it will look like when the logic is peeled open. Assistance in clarifying these views and for any help you can provide in building the base (or any part of it) will be most appreciated, sir!

LaRetta

[nburghardt]

Soren, I am working on defining the process with help from LaRetta and expect to rewrite existing solution. Thank you for your candid assessment and am happy for the assistance. Nancy

[Søren Dyhr]

Thats fine, I've kept your template on my desktop ...and wonders still why you have 31 almost identical fields on the same layout ...is it a desire to eyeball empty slots?

In my humble opinion, have you chosen the wrong tool ...it's more likley that Quark Express or such handles a solely intended visual appearance task ...than a database tool that is designed to handle data into information.

Excell is another way to eyeball slots. But as it is a waste of machinepower, since a real database handles the organization of data and only as a secondary task handles the presentation of the data.

Databases can tell you if an availiable time slot for a particuar task exists by it's inner "mechanic's" which makes the need for the 31 fields in one record redundant.

The $100 question is, should the user be bothered by being a spectator to such a task performing, or is it more likely that they just would need to know if a booking is possible or not??? In this day of age is it urgent to provide a clean cut's and not to gather noise from "nice to knows"

Now most of such reservation or booking solutions runs within the chord of this:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

Now Ray uses a customfunction which only exists under fm7 and fm8 to make the filtering happen, but importing summaries between tables, could be made to perform similar ....and because imports provided proper layouts can be lightning fast, that will they perform seemlessly.

But back to my wondering, why is it it's designed the way it is??? Such design happens ever so often, to showup in debates ...is it that a record is considered similar to an entire spreadsheet ...I'm scratching my head to grasp it???

Hopefully will you reach a solution with LaRetta, but if you have the time ...could you give some answers to my questions.

--sd