Date warnings for Fields

[sboisvert]

I am trying to create a tool for print production that will identify scheduling issues to our Marketing Managers. They will be required to fill in a field for "Availability Date" I would like then to create a field that will back out that date and either have a warning pop up or have a date displayed with a warning color (either the text or the box or both) that will tell them that their Date is not possible given it will take x amount of days to actually create and approve thier project. The dates that a project will take will depend on the type of project (I have 3 levels: New, re-format, and reprint). Is this possible to have this functionality in a field?

[bcooney]

Absolutely. I would create a table for project levels with the fields PrjLvlID, LevelName, Days (the # of days that you "back out."

Then, create a calc field in Projects that given the Availability Date, subtracts the lookedup Project Level Days, and results in a date. Now what's the logic that determines that the result date is not a possible date?

[sboisvert]

So would I replace the field (which is currently set-up as a field with the three list values as check-off boxes) with a table? Can they still select one of the three options?

Yes, I want them to recieve some kind of visual warning that the request that they have given based on the date they would like it in hand, is not possible given how many days each item will take to actually accomplish. I was hoping for a trigger that would pop-up as a warning or show the resulting date in a colored box or text.

Ultimately if their Availability date is not withing the date span it would take to complete the project, they would have a visual warning to change the date available or one would be suggested for them given these parameters...does this make sense or am I complicating things?

[bcooney]

Yes, create a ProjectLevels table. It'll help in the long run to assign a ProjectLevID to a Project. Thru the Project to ProjLevel relationship by ProjectLevID, you'll lookup that #Days field value.

I'm still not understanding the logic. Can you give an example?

[sboisvert]

Ok, say the date is today 01/01/09, if a person who is filling out the form puts an Available Date of 01/10/09 and the Project ID is New Project (a new project had a duration of say 30 days) that would mean that their Availability date should be 01/31/09 but they are saying 01/10/09. I want a field on their form to have what the actual Availabilty Date should be given the duration of the project. Also, if possible, a pop-up window stating that thier Availabilty Date was not within the guidelines of a New Project and a new proposed Availabilty Date has been provided.

Does that make sense?

Thank you so much for your help!

[bcooney]

Have a look at this demo.

Projects.fp7.zip

[sboisvert]

That's fantastic thanks!

Is it required when you have the "Message" calculation to begin with = Case ?

[bcooney]

So glad that helped clear it up for you.

No, you could use IF() if it's one of two things, given the test. Case() lets you have more tests, with a result for each, resolved in the order that they appear.

It seems every time I use IF(), I end up changing it to CASE().

[sboisvert]

Probably a better idea given that I will have three levels of Project types.

Great advice and thank you again for all of your help!

[sboisvert]

The 3 levels of a Project are selectable through a list in the field (radio button is used to select which one). Is it possible to define the durations, and thus the message and calculation for the date available, based on the one they select from the list?

I have created 3 fields (New, Edits, and Reprint) each one has auto-entered Data for the specific duration of time each level would take.

Maybe having it as a list with a radio button is not the way to go?

[bcooney]

I made Project Levels a table, and used a lookup to bring back the duration. Hard-coding values such as "New, Edits, Reprint" is not a great idea, because you might want to change from Edits to Edit and that'll break your calcs. So, use IDs.

[sboisvert]

Ok, I have created 3 tables (New, Reprint, and Edits) each having similar fields with just different durations. How is it possible for the person enter data into the form to select only one of these 3 and then only having that data show up? Basically the way I have it is that it is calculating all of the "Actual Calculated" dates for each of the 3.

What I want to happen is for them to select say, the New Project id, and then have only that Tables fields shown for the results (if they have obviously entered a date that is beyond the Duration date). Right now it's giving them the date either way which is unnecessary if they have given enough or more than enough time.

Thank you for your patience with this!!

How is it possible to post an example file?

Edited January 7, 2009 by Guest

[Lee Smith]

How is it possible to post an example file?

First, read the Help on posting a file.

Go to Menu Bar >> Help >> Frequently Asked Questions >> Posting Questions >> [color:red]Can I attach files to my post?

When posting a file to a question or reply,

First, find the file on your harddrive, and zip it.

Next go to the line, at the bottom of the browser window in the Edit Window that starts with

File Attachments 0

Valid file types: zip, txt, pdf, bmp, gif, jpg, png, sit, mov [color:red]Manage files

Click on the [color:red]Manage Files, and you will get a dialog box.

Next, click on the [color:red]Choose File Button (choose your file)

Next click on the Add Button

Next click on [color:green]Finish Button

when it is loaded, it will show in the Attached Files

HTH

Lee

Edited January 7, 2009 by Guest

[sboisvert]

Thanks Lee!

Here is an example file. I have 2 of the projects here, New and Reprint. As you can see they are both displayed but what I would like it to have them select only one and only one giving data.

duration.zip

[bcooney]

Whoa! Let's go back to basics here. I think that you should take another look at the demo I posted. In it, I have two tables: Projects and Project Types. Note that each project is a record, and that each project has one and only one project type. As soon as they enter the date_start, the calcs will evaluate.

Your data model in your demo seems completely off. You are creating a table for each project type?!

[sboisvert]

Well I did say I was a Newbie!

So the demo you provided had New as the only Project ID in the Table, how would I add the other 2? Can they be assigned as a drop-down menu or radio buttons?

I understand about the duration being entered in in an as-project basis but I would like that number to be there as a default and not something that the user can edit on their own which is why I set it as set data.

Thanks again for everything!

[bcooney]

In the demo, there are Projects in one table and each has a Project Type. When you select a prj type for a Project, you'll get the default duration for that type.

[sboisvert]

Thank you, I think I have it now. I was sidetracked by thinking I needed the separate tables but I see what you mean now.

This has been a great help thank you so much for your time and patience!