Value Date Fields

[kross]

I'm building an order form to use in our newspaper company to schedule newspaper ads. I've built two FMP files. The first (I'll call the "order form") contains the general (advertiser) information and production (size, color, etc.) information and the other contains the scheduled publications (we have 15+ newspapers) and dates. Each publication requires a new record. I use a portal to bring the scheduling information into the order form much as you would inventory into an order form.

The problem: I have one record for each scheduled publication. However, ads often run multiple weeks in the publications (i.e. Home Town News (Publication), Wednesday editions, 7/1/01, 7/8/01, and 7/15/01. Right now I have a date field, defined as a text field, that the ad reps manually list the run dates into. I search this field as well as the publication field to identify ads for a specific publication (i.e. Home Town News and 7/1/01). This will give me a list of ads that run in the 7/1/01 Home Town News.

This works well enough in theory, but I foresee ad reps writing the wrong dates and/or improperly formatted dates that the search will not identify (i.e. 07/01/01 or 7/2/2001).

I hope this makes sense to you. Can anyone offer me a solution to this problem?

[email protected]

[MeltDown]

quote:

---

Originally posted by kross:

Right now I have a date field, defined as a text field, that the ad reps manually list the run dates into.

[email protected]

---

Hey!! My sister works for Sound Publishing, and she would never enter the wrong info into a date field!

But seriously, is there a good reason to have your date field set up to be a text field? As a 'safety' precaution, I would have the entry field set up as a DATE field, so FM will check to make sure the entry is a valid date. If you need to display the date as TEXT, you could use a calc field to convert the date to text. I hope this helps!

And say hey to 'Rain' for me!

BTW - Did you initially use a Text field so you could have multiple dates all entered in one field on the record? If so, another solution may be to use a value list for the dates, and format the field on the form to be check-boxes based on the 'weeks' value list. This would take up more room on your form, especially if you have a lot of dates to list, but would eliminate data-entry errors and still put all of the dates in the same Text field. And its faster for data-entry than actually typing dates.

[ June 28, 2001: Message edited by: MeltDown ]

[kross]

And say hey to 'Rain' for me!

Does your sister go by 'Rain'? I'm not sure I know her. What office/department does she work in?

The answer to your question is yes, I set up the date field as a text field to list multiple run dates. I had thought of using a value list, but felt I'd have to list 356+ dates or update the list every couple of days.

Updating it every couple of days would be a huge admin pain and listing 356+ dates would be a problem because the order syncs to a Palm Pilot which our ad reps use to schedule their ads. Long list of dates would make scrolling on a palm pilot a pain.

Any other thoughts???

[MeltDown]

quote:

---

Originally posted by kross:

Does your sister go by 'Rain'? I'm not sure I know her. What office/department does she work in?

---

Her official name is 'Lorraine'...she works on large accounts out of the Bainbridge Island office - and she is a lot of fun to know!

Back to your question...

quote:

---

I set up the date field as a text field to list multiple run dates. I had thought of using a value list, but felt I'd have to list 356+ dates or update the list every couple of days....and listing 356+ dates would be a problem...

---

I was thinking more along the lines of using calculated fields and building conditional value lists to selectively display dates as checkboxes.

There are lots of ways to do this. For example, you probably already have a separate database listing ALL dates, and the Publications which come out on those dates. You could build a relationship to this database based on the entry in the 'Publication' field, then use a conditional value list to display appropriate dates (for example, only Wednesdays would be included in the ValueList for 'Home Town News - Wednesday Edition'). Now 52 'Wednesdays' would still be too many to scroll through, so you could ask for the month/quarter/region/whatever to be selected first, then display dates for that month/quarter/region/whatever, or set up a calculated field to display dates only if they fall within one or two months of Status(CurrentDate), etc. Once you have the relationships and calculations built, it should require limited maintenance.

Unfortunately, conditional lists don't work well on the web, so this might not work if you are building for that environment.

Other suggestions:

1) Have a separate date field for entering the dates. Use an 'Add Date' button to trigger a script that will copy the correctly added date to the text field. (This may work well, or could be cumbersome for Ad Reps who are trying to enter the dates while talking to their clients.)

2) You probably already have this, but if not, consider a check box, button or pull-down so the Reps can indicate 'every' Wednesday, Sunday, whatever for long-running ads - the Ad rep would only enter start and end dates. The start and end fields would be formated to be date fields, and the dates in between would be calculated. Of course, the dates could all be copied into your text field if desired.

Thats all I can think of...good luck!

[kross]

quote:

---

Originally posted by MeltDown:

Her official name is 'Lorraine'...she works on large accounts out of the Bainbridge Island office - and she is a lot of fun to know!

---

Oh - I know Lorraine. You're right, she'd never not enter something correct. She's a good one. I'll tell her hi and that you're helping me out.

quote:

---

Originally posted by MeltDown:

Unfortunately, conditional lists don't work well on the web, so this might not work if you are building for that environment.

---

And unfortunately, conditional lists don't work well on Palm Pilot syncs either. Good suggestions. You've given some direction to go, I'll try a few things and let you know what I find. If you think of anything else, I'd appreciate it.

Thanks again--

[ July 03, 2001: Message edited by: kross ]