This is bothering me - Data Entry Script

[Silvertop]

Below is my Data Entry Script. It works fine, so far as I can tell.

It does not seem logical to me though. This...

# if user presses 'Cancel'

If [Get ( LastMessageChoice ) = 2]

Revert Record/Request []

... seems to need to come before I have even make a

New Record/Request

?

John

================================

Allow User Abort [Off]

Set Error Capture [On]

Go to Layout ["Entry - Monthly" (Link)]

Beep

Show Custom Dialog ["Enter the Issue Number"; "Enter the Issue Number, before adding any new members"; Link::gFirst]

# if user presses 'Cancel'

If [Get ( LastMessageChoice ) = 2]

Revert Record/Request []

Go to Layout ["Main Menu" (Link)]

Enter Browse Mode []

Show All Records

Unsort Records

Else

New Record/Request

End If

[LaRetta]

Changes indicated in green...

Allow User Abort [Off]

Set Error Capture [On]

Go to Layout ["Entry - Monthly" (Link)]

Beep

Show Custom Dialog ["Enter the Issue Number"; "Enter the Issue Number, before adding any new members"; Link::gFirst]

# if user presses 'Cancel'

If [Get ( LastMessageChoice ) = 2]

Revert Record/Request [] [color:green]... remove. Unnecessary. It deals with data entry.

Enter Browse Mode [] [color:green]... remove. No need to go to Browse if you've not been in Preview or Find

Show All Records

Unsort Records [color:green]... remove. A Show All Records automatically unsorts them.

Else [color:green]... remove. There is no Else because you will halt the script if User cancels.

Go to Layout ["Main Menu" (Link)]

Halt Script [color:green]... added

End If

New Record/Request [color:green]... moved to OUTSIDE the End If

LaRetta

[LaRetta]

"Enter the Issue Number, before adding any new members"

I should clarify ... it is strange to have them add an Issue Number before they create the record unless you are capturing it in a global text and then testing it? But I see no test so what difference does it make when they enter this information? If they are required to enter an Issue Number (such as from a Form in front of them) then it would make more sense to create the new record first.

So without knowing more about your process, it will be difficult to give the best answers. Are you possibly creating TWO new records - one for a new Issue and then one for a new Member? Using Revert indicates you are setting a standard field - which wouldn't make sense either. If it is a global, you can just clear it yourself if they cancel. Please explain why you think Revert is necessary. In fact, I don't recall using it more than once or twice in two years! That doesn't mean there aren't reasons ... just that you haven't explained them.

LaRetta

[Silvertop]

Thank you LaRetta, for the great mini-tutorial. Some people are great at learning from books, others (me included) are better at the 'watch & copy' style of learning. More please.

The script is altered and working fine now.

Unsort Records ... remove. A Show All Records automatically unsorts them.

I did not know that. :

Halt Script ... added

I need to ask, "what is the difference between halt/exit script?"

it is strange to have them add an Issue Number before they create the record unless you are capturing it in a global text

It is a global Link::gFirst

and then testing it?

You have lost me now - I don't understand (how/why test it?). You appear to have discovered another weakness :

But I see no test so what difference does it make when they enter this information?

I was just attempting to save some data entry keystrokes, as the Issue Number only changes bi-monthly, and the user may enter groups of new data at one session. (I feel a complete re-write coming on : )

Thanks again for all the help so far.

John

[LaRetta]

Hi John,

Halt Script does just that, ie, no matter where the User is; no matter what additional script-lines are listed; no matter if this is a sub-script called from another, it ends your process. I like to use it to help me envision what is finished (mentally taking it off my plate, so to speak).

Exit Script exits only the current script. If the running script was called as a sub-script, the original script will continue to run. Exit Script would have worked just as well here (since it was the only running script). I use the one appropriate to my needs AND to add clarity. And it is in the moment that I am writing the script that I have the best understanding of the process (and whether other scripts are running). So it is a visual cue as well.

Questions about the Issue Number:

• Do you know each new Issue Number when it comes out? Do you know when it should go into effect (a specific date)?
  
• How do your Users find out the number changed? Is it on a document they type from or do they get a memo?
  
• What if they type the number wrong or what if they delete it within the Custom Dialog?
  
• Do you have any protections in case they type it wrong, ie, checking that the PATTERN of the number is within spec? What *is* the pattern of this Issue Number?
  
• What is the importance of this Issue Number? If it is wrong, what happens?
  

There are certainly better ways of handling pre-fill of the Issue Number than to bring up a Custom Dialog on each new record. And there are ways to further protect your data-entry process. But I will need these questions answered first.

LaRetta

[Silvertop]

1 Do you know each new Issue Number when it comes out? 2 Do you know when it should go into effect (a specific date)? 3 How do your Users find out the number changed? Is it on a document they type from or do they get a memo?

1. Yes 2. In theory yes - in practice, no. It would be nice if it were on e.g. the 1st. of the month, but no. 3. Memo

4 What if they type the number wrong or what if they delete it within the Custom Dialog?

Ah, yes, problems.

5 Do you have any protections in case they type it wrong, ie, checking that the PATTERN of the number is within spec? What *is* the pattern of this Issue Number?

No protection - yet! : The pattern is 2digits/1digit(between 1 & 6) i.e. 11/1,11/2,...11/6,12/1 etc.

6 What is the importance of this Issue Number? If it is wrong, what happens?

It is important. there are 2 types of subscriptions annual and non-annual. For example the renewal for the annual sub. whose first sub. was 10/5 would be 11/5.

John

[LaRetta]

Hi John, the reason I'm asking all the questions about the Issue Number is because, in additiion to just giving you a simple script-fix to trap for patterns on the number, I think it indicates a probable flaw in your design. But I don't KNOW your design so I'm not sure how to proceed here.

Consider this ... why are Users entering the Issue Number? Does it come from program outside of FM? Because if not, you should KNOW the prior Issue Number for any customer and insert it automatically. The first time this User changes the number, *IT* will become the last valid Issue Number for that customer. So do you have a Customer table and are prior Issue Numbers stored within FM? Are you using uniqueIDs to join your tables or is this pretty-much flat file structure?

I can give you some protections when they enter the Issue Number. I can even provide protection (within the Custom Dialog) for empty numbers. We can even pre-fill that Issue Number under most occassions. But is this Issue Number problem actually pointing to a larger problem, ie, you aren't using relationships to handle this process for you. Can you tell me more about your structure and explain where/how this Issue Numbers are born? I also question the Issue Number itself - something as seemingly important as the Issue Number is controlled only by data which appears to be month/year? Sirens go off all over the place for me.

LaRetta :wink2:

[Silvertop]

OK (remembering that I am a Novice, converting an existing manual database to a computer based DB).

The FM database at this point is not too much more than a card system on computer (Names, Addresses etc). Hopefully it will evolve. This is a DB for the subscriptions for a bi-monthly (approx.) magazine for an NFP.

The DB needs to be 'simple' both to the user and to me (for maintainance purposes - hopefully I will acquire more FM expertise as I progress).

>> why are Users entering the Issue Number?

When someone initially subscribes, their details are added with some extra fields including

'Code' - a unique 5 digit number,

'Issue' - the number of the first issue they receive (in the form [2 digits/1 digit(between 1 & 6)] i.e. 11/1,11/2,...11/6,12/1 etc.) The first 2 digits represent a 'year' i.e. the first issue would have been 01/01 (year 1/issue1). Currently about 20/4.

'created' the date the record is created. This is in the forlorn hope that they will eventually switch to a date based system rather then the issue system.

>> explain where/how this Issue Numbers are born?

The magazines are not produced on a specific date. The sequence is approx. every two months, but can be a couple of weeks early or more likely late. Users are advised when the number changes. [color:blue]I never said it was a great way to do things :/

The Issue Numbers are the basis for renewals. i.e joined = 11/4, due for renewal = 12/4.

>> I also question the Issue Number itself - something as seemingly important as the Issue Number is controlled only by data which appears to be month/year?

You have lost me here... The Issue numbers are basically 'set in stone', i.e. 20/4 will be followed by 20/5, 20/5 by 20/6, 20/6 by 21/1 etc.

>> do you have a Customer table and are prior Issue Numbers stored within FM? Are you using uniqueIDs to join your tables or is this pretty-much flat file structure?

This is currently pretty much just one file. I don't (yet) have separate tables and/or relationships, though I am happy to go that way.

>> I can give you some protections when they enter the Issue Number. I can even provide protection (within the Custom Dialog) for empty numbers. We can even pre-fill that Issue Number under most occasions.

All needed I think.

My apologies for the convoluted answer. In my defence, I did not set up the original system, [color:blue]so I'm hoping you won't shoot the messenger!

I am willing to change the system where required to provide a solid db.

I hope I've answered most of the queries, but keep asking if I have not.

Finally, thanks for the continuing help.

John (off to a wedding for a few hours...)

[LaRetta]

Hi John,

I attempted to strike a compromise. I wanted to show that (by using relationships), you could actually make things easier and safer for you and your Users. If you view the graph in this sample file, you will see I added Clients table. This is what I would suggest. But you can wait to do that part! The grey tables below (in the graph), shows how you can (sort of) accomplish the same thing by joining your subscriptions to itself in a self-join. The grey TOs have nothing to do with the functionality of the demo – only to provide visual of a self-join (if you can’t add a Clients table now). Now to the Issue (pun intended):/

Problem: Even if the PATTERN of the Issue Number is valid, a User can add the same Issue for the same Client twice.

Solution: Join to their prior entries. I chose to display message here instead of using field validation. I dislike field-level validation and Users dislike it as well. I wanted you to see a few different ways of validating and assisting Users through their process.

Problem: A User can enter a blank or invalid Issue Number.

Solution: Take away possibility of error. I'm unsure whether you want your Users adding Issue Numbers (I suggest against it) or whether the 'person who sends the memos' should be the only one allowed to add an Issue but I gave you the option on the main layout. This small Issues table allows 1) more control over the numbers and 2) hopefully will move you to a date-based system (note the MagazineDate). Using dates (even though the Users may only see the Issue Number) is powerful because you could then make sure an added Issue for a Client is more CURRENT than their last subscription. I included it to step you that direction. In the meantime, an Issue table will remove 70% of User interaction with those Issue Numbers. If you can restrict entering new Issue Numbers to the Issue table (via Add Issue), then you can remove that field-level validation on the Issue Number (on User layout) and restrict Users to ONLY selecting a valid Issue (via popup). I was unsure which way you wanted to go, so I validated User at field-level. But it is harsh from User perspective. I was unsure if a new subscription could ONLY be the current subscription or whether it would be just one subscription forward from a Client’s last.

Problem: Well, maybe not … but that Code you mentioned scares me as well. What do you mean by Unique? Do Users enter it or is it generated as unique serial in FM? I set it up as FM-generated serial. I would NOT let Users enter a code which must be unique.

Solution: At the minimum, add 'unique' validation to your Code but I would prefer removing that from User entry if possible.

Your original script required User interaction with Custom Dialog on every entry. I believe that the approach I’ve outlined with be more User-friendly, protect from severe data-entry errors AND move you towards relational design. I hope it is useful.

UPDATE: If you view the validations (field-level and within the script), you will see I allowed two digits on the second portion of the Issue Number. You say it should be one digit but your example of "i.e. the first issue would have been 01/01 (year 1/issue1)" indicated otherwise. I used Let() to make it clear what the calcs were doing so you could easily modify them if need be.

LaRetta :wink2:

Subscriptions.zip

[Silvertop]

Hello LaRetta

Some food for thought there

Thanks for the detailed reply and particularly the attachment. I intend to incorporate as many of your suggestions as practicable in the near future. [color:blue]Solution: At the minimum, add 'unique' validation to your Code. = already done.

I just need to look further at your attachment (quite a complicated calculation in there, for a novice ), to make sure I understand what's going on.

Again, thanks for all the help. No doubt, I will be back to these forums shortly with more problems needing solving.

Thanks

John