How to Add Record to a set.

[davtech]

I'm new with Alpha and hate to bother people with what may be simple but I am confused. In Visual Basic I could have a form with fields from differnt tables and just run some SQL code to open a recordset made up of the tables and then add a new record and pull the data from the form's fields and enter them into the tables at one time. Now in Alpha I am running into all kinds of confusion when I am trying to do the same thing. The form I am using is based on a set consisting of 4 tables. I want the user to be able to select "Enter New Record" and then enter the information into the fields on the form for all 4 tables and then be able to save it all in one operation.

Is this possible? Do I need to go about this another way?

[Tbaker]

Re: How to Add Record to a set.

Greg

How is the set linked parent to child child child or parent to child with grand kids. Is is 1:1 or is it 1:M.

Tom Baker

[Tom Cone Jr]

Re: How to Add Record to a set.

Greg, Alpha Five is not Visual Basic or Access. It's closer to FoxPro or Visual FoxPro. It's very close to Clipper. Data entry through forms occurs table by table. Even in set based forms the fields are bound to the underlying tables all the time. You don't type in text and then run some separate process to populate records in tables. When you're typing in the text you're entering text into the buffer for the current table record automatically. In set based forms this can be a bit confusing. Fortunately Alpha makes things easier for us by trying hard to automatically keep things in sync. In the typical Alpha Five data entry form the user enters field values into a new parent table record. When focus shifts to a browse object displaying linked child table records the form automatically commits the parent table edits to disk. In like manner, when focus moves from row to row inside a browse Alpha commits the edits just entered in the previous row. This approach makes sense for most applications and is very easy to implement. In Alpha Five there is never a reason to permit the user to enter an orphan child table record first. Enter the parent, save it, then enter linked children. The mechanisms built in to Alpha Five forms makes this dead easy for anyone. But there's a price to be paid. In those apps where the designer wants to defer commiting multiple records (or single records in multiple tables) to disk until all have been entered and validated, Alpha Five's forms get in the way. There's no easy way to roll back a "transaction" consisting of multiple records in multiple tables. If this is a requirement for your application the Alpha Five solution would be: (a) don't use forms to collect your inputs... use xdialogs instead; or (b) do the data entry using Alpha Five forms based on a set of tables that serve as interim data stores... enter them all... validate them... then copy them to your live tables.

-- tom

ps Your Subject Heading for this thread suggests (to me anyway) that you think there's such a thing as a "set record". That's a bogus concept. Sets don't have records. In Alpha Five only tables have records. If your set has 5 tables, then data is stored in each table, in single records, one at a time. The set simply makes it possible to display related records together. You can never enter a single record to a set, if you see what I mean. You have to enter single records to each table in the set. If memory serves you've received this advice before. How's your study of the "default form" for the Invoice set in AlphaSports coming along?

[Davidk]

Re: How to Add Record to a set.

Since your form is based on a set - and the set is made up of the 4 tables - then the fields on your form are from the set. When you create your form, you're dragging the available fields from all 4 tables onto your form - the fields are "bound" to your tables.

Your user clicks a button which adds a new record and then fills in the fields on the form which are already connected to their underlying tables. Saving the data is more or less automatic, but a Save button can perform a Save operation. Do you need specific details?

If your database is one to many then you can place Browses on your form - like a grid - which will contain fields from the many side.

Just trying creating a new form based on your 4 table set and you'll see how it works.

I assume you were not using a data connector in VB, rather a form with unbound fields.

A5 works more like Access than VB

[davtech]

Re: How to Add Record to a set.

So if in my example I have a set consisting of 4 tables and the fields from these tables are all on one form will the user need to enter the data into the fields for the parent table first or can all fields be entered and then a save button selected?

Tom - yes I have studied the default form for the set some and it seems like I should be able to enter a new record and then enter the data for all fields and then save it. I have a parent table linked to a child table via a SSN field but I had the SSN field from the child table on the form instead of the parent one. This was part of my problems I believe. If I can have the fields from all 4 tables on this form and the user can just enter their data and then hit save I should be fine. I discovered already that rolling back a transaction was not going to happen, but I'm not to worried about that.

David - Yeah I never liked working with the data connecter and just used the unbound fields so I could control everything, but that takes a lot of time to get everything you want and Alpha makes life so much sweeter. I just need to get into the Alpha mindset and learn the new ways of doing things.

[davtech]

Re: How to Add Record to a set.

Ok I did some more testing with this. I have a form based on a set consisting of 4 tables. Fields from all 4 tables are on this one form. The parent tables is linked to a child table via the SSN field. Regardless of which fields I use ( the one from the parent or the child) if I start entering data into a field of the child table first I get an error message saying the field is required. I'm assuming if I understood Tom's post correctly I have to deal with record entry by single table only. In other words the set is useful for displaying related data on a form, but I need to set things up so that the user is entering a record to a single table only. In this case the user may need to go to 4 different forms to get all of the data in? I know I could use the options a and b that Tom explained in his post, but that would be a lot of extra work. If this is the case I may need to go back to the drawing board on how my user interface should be laid out.

The parent to child relationship is one to one by the way.

[Tom Cone Jr]

Re: How to Add Record to a set.

Good morning, Greg.

This should not be that hard. Maybe we're misunderstanding what you are saying. Can you post (upload as attachment) a copy of your database (after purging or mangling private data of course) ?

Later...

Greg, what I've been saying about the mechanisms built in to Alpha's Forms for automatically saving records while using set based forms may have been misleading, or worse, wrong. I've been thinking of sets that have one to many links, not sets that have one-to-one links. I've never seen a set with 4 tables that had one parent, and 3 children, using one-to-one links. The behavior of forms based on this kind of set will be different from what I've described.

The best thing is for you to post (upload as attachment) what you're asking about so we can talk specifics, and not generalities. I'll try to pay more attention to what you're saying.

Stil later ...

Here's an example using one to one links in a set based form. Maybe it will be more useful to you. [ Hopefully it can be used to talk specifics if you have further questions. ]

The second example includes a "Save Transaction" button which uses an xdialog to collect inputs before committing anything to disk.

-- tom

[Stan Mathews]

Re: How to Add Record to a set.

if I start entering data into a field of the child table first I get an error message saying the field is required. I'm assuming if I understood Tom's post correctly I have to deal with record entry by single table only.

Yes and no.

If you begin entry into a record on a form based on a set, the parent fields are generally filled in first allowing the saving of the parent record. This is why you are getting the "field is required" message when you try to enter the child table fields first.

As Tom stated,

In the typical Alpha Five data entry form the user enters field values into a new parent table record. When focus shifts to a browse object displaying linked child table records the form automatically commits the parent table edits to disk.

When that parent record is committed, the field rules are enforced. If you have a required field, Alpha tells you.

It is possible to wrongly create a data entry form for a set where the required field is not on the form. That would make it impossible to perform data entry because the parent table field rules could not be satisfied.

[davtech]

Re: How to Add Record to a set.

Thanks for your help Tom and Stan. I think I will be doing some redesign on my form. I'm going to take some time and study your attachments Tom and see if I can get a better understanding of how things are working. This being Sunday and Father's day I will probably not do anything today though. I appreciate all of the help I have been getting on this board it really helps. I come from a Visual Basic programming background but my database development experience is self taught so I still have a lot to learn and now especially with Alpha, but its worth it as Alpha does make everything so much easier.

[davtech]

Re: How to Add Record to a set.

Tom - Thanks so much for your time and assistance, your examples made a world of difference in helping me to see what is going on. Again, in Visual Basic I controlled everything as to how the data was saved and to which table and when so this has been different for me.

My parent table for this set is called "referrals" and holds the service requests records. A field for the claimant's SSN is here linking it to the claimant's table via SSN. This was a one to one relationship which I now see this is not going to work properly for what I'm trying to do. There should never be more than one instance of any SSN in the Claimant's table, so what I need to do is redesign my set with the Claimant's table as the parent and the referral table as the child with a one to many relationship. From there I add the Claimant SSN field to the form, make it required and the other fields from the set as well and all should work normally as described in your previous posts when editing or saving a new record.

[Tom Cone Jr]

Re: How to Add Record to a set.

Greg, that's good to hear. Drop me a line next time you're headed to Tampa. I'm based in Lutz, about 19 mi north of downtown. Would enjoy getting together. -- tom

[davtech]

Re: How to Add Record to a set.

I'll do that but be warned, I'll probably have a list of Alpha questions to pick your mind about. :)