Data entry inhibited on form based on a set

[sgerber]

I'm wondering if Alpha Five V11 is more restrictive than V1 in the following situation.

I have an invoice application with the invoice header record as the parent, 1 to 1 with the customer record, and 1 to many with the invoice line items.

The customer number has a validation field rule which forces it to be unique. This is a lookup field in the set and the customer fields fill the invoice header. This should be irrelevant but the invoice number is an autoincrement field.

The invoice form seems to work fine in most regards, but if I change any existing invoice header field and then either advance to the next record or explicitly save the record I get an error "Customer ID Must be Unique". That message can only come from the Customer table's validation field rule on Customer No. The Invoice set's field rule for that field shows the same validation rule. I honestly do not know whether that field rule exists separately in both the Customer table and the Invoice set or whether Alpha Five simply shows it when you look at the set's field rules.

This problem does not exist in A5 V1.

I don't know why A5 would think that a duplicate customer record is being created when the fields of the set are saved. The invoice form uses a different customer no. field from the one in the customer file, though of course they have the same value.

From what little I've just told you, can you suggest any possible culprits, especially something in the definitions that V1 would forgive and V11 not so forgiving.

I wanted to get this question posted first but I'm now going to explore whether the Customer table's field rule truly exists twice, once in the Customer table and again in the Invoice set. If it doesn't need to be in the set, I'll remove it. It would seem to me that it's sufficient to have the field rule only in the Customer table to protect it there, because if the user tried to add a duplicate record it would happen outside the set as a direct update to the lookup table.

I hope this is sufficient information for you to give me a couple problem areas to investigate.

Thanks so much ... Sam

[Ray in Capetown]

Re: Data entry inhibited on form based on a set

Sam, when you view or edit field rules in a set you are actually working on the underlying table.
The set simply provides linkages.
Your set as described for invoicing is a pretty standard simple format.
I'm trying to picture why you get the error. No explanation comes to mind.
If you send yours or make a simple example database with a few records to illustrate you will get some valuable advice here.

[Stan Mathews]

Re: Data entry inhibited on form based on a set

I have an invoice application with the invoice header record as the parent, 1 to 1 with the customer record, and 1 to many with the invoice line items.

Your description is exactly what is the basis for the AlphaSports sample database (minus the link from invoice line items to products and from products to vendors). You might take a look at that and see how yours differs.

[sgerber]

Re: Data entry inhibited on form based on a set

Thanks for the explanation of the field rules, Ray.

I'll have to give some thought as to packaging small subsets of my database. Is this correct: I duplicate the important elements like the set and it 4 tables, and the form. Skinny them down. There should be around 5 files per table, yes, and fewer for the set. Then zip them all together and upload that with my post. About right?

Thanks for the suggestion, Ray.

[sgerber]

Re: Data entry inhibited on form based on a set

Good idea, Stan, I'll try to do that Tuesday.

[sgerber]

Re: Data entry inhibited on form based on a set

This sample application had no integrity at all built into it Stan. The customer no. was not even a required field, let alone validity checking for uniqueness. Hardly any fields had field rules specified other than the lookups.

Very disappointing, couldn't learn a thing from it.

I did have some fun though changing the autoincrementing customer number from "00000061" to "abc", and then to "" which orphaned the invoice.

That was enough fun for one night, however.

[Ted Giles]

Re: Data entry inhibited on form based on a set

Couple of points Sam.
1) In a commecrial application, you shouldn't change a header record once it's saved as that is the root for all audit trail functions.
2) The design of the set is important. See attached, which also has a lookup based on the customer table and a browse showing all invoices.

[Tom Cone Jr]

Re: Data entry inhibited on form based on a set

Sam,

Why include the customer table in your data entry set? Most would consider it a mistake, since a change to a customer field value (while entering an invoice transaction) would ripple through all transactions for that customer. A common misconception is that it's necessary to link the lookup table into the set in order to use a table lookup field rule. That's wrong. It's not necessary for the customer table to be linked into the set in order to define and use a table lookup field rule in the invoice header table. In my own work I'll often include the customer table in a set that will be used for reporting, but not for sets being used to support data entry forms. So, I ask again. Why is the customer table part of your data entry set for an invoice?

[sgerber]

Re: Data entry inhibited on form based on a set

Tom, thanks for writing. I think you've made a very important point that I need to explore with you a bit.

I agree (I think) that the Customer table does not have to be defined as part of the Invoice set. It's only being used by the Lookup rule in the Invoice Header, in order to pull the customer information into the header. For this reason I will probably remove it..

However, I want to take exception to your statement:

a change to a customer field value (while entering an invoice transaction) would ripple through all transactions for that customer.

For one thing we're not entering directly into the Customer table on the invoice form. We're entering into a field value in the Header record which should only affect the current invoice. Secondly, if we actually were entering into the Customer table directly (because it's part of the set and assuming we defined the form field as CUSTOMER->FIELDNAME) I still do not see that it will ripple into all the previous invoices. I agree it would if we addressed each of the previous invoices, one at a time, and used the lookup field to download the current Customer record into that invoice.

I need you, Tom, to agree that in our scenario Alpha Five is not going to ripple any customer changes into all previous invoice headers for that customer. This seems especially true because of Referential Integrity. The Invoice set makes the Invoice Header parent to the Customer, one to one. Within the confines of this set, the Customer record cannot address the other invoices.

Tom, I'm nervously awaiting your response.

Thanks so much for adding to this discussion ... Sam

[sgerber]

Re: Data entry inhibited on form based on a set

After further investigation, the title of this thread "Data entry inhibited on form based on a set " is no longer accurate.

The problem also affects the Customer record directly, having nothing to do with the set. I'm going to enter a new thread for this called something like "Data edits prevented by Validation Field Rule".

Thanks to all you who so far contributed to this thread.

Best ... Sam Gerber

[Tom Cone Jr]

Re: Data entry inhibited on form based on a set

Sam, you haven't shown us the database, so forgive me for making the wrong assumptions regarding your design.

Since your data entry form is based on a set, I assumed you had fields from the Customer table in the layout. Any change to a field in the customer table would appear in all other form displays that are based on the same set. i.e. if the Lastname field from the customer table was present in the set based form, and you changed the spelling of the lastname in a single invoice transaction, the same change would appear in all other forms and reports containing the lastname field from the customer table. this effect is the "ripple" I was referencing. Naturally, if you're retrieving the lastname from the customer table and storing it in the invoice header table, this kind of "ripple" will not occur in this situation.

-- tom

[sgerber]

Re: Data entry inhibited on form based on a set

Watta relief, Tom. I'm very sorry I wasn't clear that our Invoice Set only had fields entered into the Invoice Header record.

15 years ago Tom I was an ace with A5. The application was for my son who owns a store. I haven't touched A5 since then and my skill level ... shall we say ... isn't quite current. As a result I've got a learning curve with A11 that's quite a bit steeper than I had hoped. Fortunately, as was true 15 years ago, the Alpha Five community is very supportive of its brethren and the support I receive here is the best in the country.

In case you were curious, I've picked up A5 again because computers that run 16bit code are going the way of the dinosaur. If my son needed a new computer with WIN7 64bit (and that's all they issue these days) Version 1 won't run unless I implement the "XP Box". That's a good solution since it's all Microsoft and they supply XP with no activation required. Then I just read that WIN8 dropped "XP Mode". I thought "this is crap, I'm gonna try to bring A5 into the 21st century".

You Alphaholics may see quite a bit of me until I complete this project. Don't let me scare you away.

Regards ... Sam

[Tom Cone Jr]

Re: Data entry inhibited on form based on a set

Understood.

You might want to get acquainted with the "Zip" utility built into the tools menu of the Alpha Five control panel. Can be used to quickly zip the entire database for backup, or for posting here. If your data files are large you may want to delete much of the data and work with a smaller "version" as you complete the development work in vers 11.

Also, you might want to get acquainted with the file upload (attachment) facility of this message board. To get there isn't immediately obvious. You must begin a reply, then choose the "Go Advanced" button, then scroll down to "manage attachments". Then you browse to the desired file, and upload it, then, finally, you submit your reply.

-- tom

[sgerber]

Re: Data entry inhibited on form based on a set

Thanks for the usability tips. I'm hoping I don't have to submit my databases to receive assistance. It's one thing to pare down their size, but there are fields that my son (who runs a store and uses the application) wouldn't want the world to see, like credit card information, addresses of real people, their phone numbers, etc. To accommodate him I may not have enough info left to test with.

We'll see ... Sam

[Tom Cone Jr]

Re: Data entry inhibited on form based on a set

Yes, maintaining the privacy of real data is vital. But the task is not as daunting as it may seem. If you delete all but a handful of records you can easily "sanitize" the remaining records in most cases.