Combine, in a form, fields from 2 tables, and append into a 3rd table 2

[DonOliver]

Using VFP7 on Windows 7.

I have 3 tables: Members, Volunteers, and VolunteerTasks.

On the Members form, I have a combo box, which allows selecting a task from VolunteerTasks.

There is also a button, whose User Procedure should gather a few fields from the record
open in the Members table, combine it with the selection from the combo box, and
APPEND them to the Volunteers table.

I have been going around this for a long time, and getting nowhere because, once the
combo box is activated, Members is no longer open.

Is what I am attempting to do even possible?

Thanks for any hints,
Don Oliver

 

[JRB-Bldr]

On the Members form, I have a combo box, which allows selecting a task from VolunteerTasks.

I assume that the value selected here has some bearing on the second operation, but you are not clear as to how it is used.

Regardless, you can save off the selected value into a Form Property which you add to the Form, or just have code in your Button's Click method to 'read' the ComboBox's Displayvalue and use it as needed.

There is also a button, whose User Procedure should gather a few fields from the record open in the Members table, combine it with the selection from the combo box, and APPEND them to the Volunteers table.

If you have 'read' the ComboBox's DisplayValue you can now use it as needed in a SQL Query of the Member's table.

Then to add that query's result to the 3rd table you can do something like:

SELECT Volunteers
APPEND FROM DBF('query')

Good Luck,
JRB-Bldr

 

[DonOliver]

Thanks for your answer, but I am still confused. Maybe because my post was not clear.
The selection must be made from the combo box before the procedure to do the rest.

After that, how can I select fields from the current Members record, and combine them with
the combo box selection from the VolunteersTask table.
Once I have them combined, I already know how to append the combination to the Volunteers table.

Don

 

[Olaf Doschke]

How are you addressing fields? By field name only?

If you only use field names, a table must be the currently selected table. Indeed it's the workarea, that is selected, not the table, but that aside: you can address the fields of a the current record in a currently not selected table by addressing it with the alias (which is the VFP term for the workarea name and typically identical to the table name), followed by dot and then the field name. So instead of firstname, lastname, you address these fields with members.firstname, members.lastname.

The member table is not closed when the combobox activates the volunteertasks, it's just not selected, and so addressing firstname or lastname alone will lead to an error, that these variables are not available. That's because VFP doesn't look in every opened table, if that field is found anywhere. That would slow it down awfully. The bad thing about it is, if it's not found as a field, VFP continues searching for a variable, and if also no variable by that name is found errors with the message variable 'x' not found.

So in the end, you can easily pull together fields from several tables, by using the full qualified name:

insert into volunteers (id, volunteertaskid) values (members.id, volunteertasks.id)

And you should not have more than these two fields from members and volunteertasks in the volunteers table. There could be further fields, like MemberStartedTaskDate, MemberEndedTaskDate, for example, but that could also be in the volunteertask table, if you are not interested in the time of the single volunteers involved, but only the overal duration.

Don't copy over name or other data, or you cause redundancy in your database. To show a list of volunteers with their name you later rather

Select mem.*, vt.* from volunteers vol left join members mem on vol.memberid = mem.id left join volunteertasks vt on vol.volunteertaskid = vt.id

Bye, Olaf.

 

[JRB-Bldr]

The selection must be made from the combo box before the procedure to do the rest.

Getting the data together and into the proper format is no problem.
But what I am confused by is the sequence of events that need to happen.

Obviously you cannot do a necessary action BEFORE the required data is acquired.

So you only need to acquire the required data (example: make selection from ComboBox, etc.) and THEN do the necessary action on that data.

And your Form objects have a variety of associated Methods (Click, InteractiveChange, When, Valid, etc.) which can be used to do whatever you want, whenever you want the action to occur.

And within those methods you can separately access any data table/cursor being used as a DataSource, RowSource by another Form Object.

So, I guess that I am still confused as to what is the challenge here.
Perhaps Olaf has address your issue. If so, that's great.
Or maybe we need better clarification of your issue so that we can better understand how to assist you.

Good Luck,
JRB-Bldr

 

[DonOliver]

Thanks so much to Olaf and JBR-Bldr for their help.
Olaf's reply makes what has to be done very clear.

Don

 

[Olaf Doschke]

I still have one design mistake. In the volunter table I indirectly made the primarykey field a foreign key field you fill with memberid. That can be done, but has some implications you may not want: 1. A primary key, which also is a foreign key, 2. each member can then only take one volunteertask. so rather have a id, memberid and volunteertaskid field in the volunteer table. The id is just a normal id, autoinc integer to have a primary key for each record.

That aside, the main point is to address fields by tablename.fieldname.

Bye, Olaf.