Adding a new record in a related table

Archived from the Xataface Users forum.

jhenry — Sun Jul 12, 2009 2:34 pm

I have two related tables Work Orders and Time Entry. I need to be able to add a new time entry when I am in the process of adding a new work order. I can manually create the time entry and then select it after the fact but I don’t want the end users to have to do this. The relationships.ini for the work order table is as follows:

[Time_Entry]

Time_Entry.timeentry_id = “$timeentry_id”

Thanks in advance for a great program. And I appreciate the help.

jhenry — Sun Jul 12, 2009 5:00 pm

I have found some description of the grid function that looks like it might be what I am looking for but have not been able to find the appropriate usage for this directive. If you could point me the correct way I would appreciate it.

Jean — Mon Jul 13, 2009 12:43 am

Hello,

Did you look at triggers ?

http://xataface.com/documentation/tutorial/getting_started-0.5.x/triggers

Jean

shannah — Mon Jul 13, 2009 9:21 am

Here is an example using the grid widget.

Given a relationship named “Appointments”, set up a transient field called “appointment_grid” (this can be named anything) in the fields.ini file as follows:

Code: Select all: [appointment_grid] transient=1 relationship=Appointments widget:type=grid widget:columns="aaf_year_from,aaf_year_to,aaf_name,aaf_organization_1,aaf_organization_2,aaf_city,aaf_province,aaf_country"

Things to note:

transient=1 is required as this tells xataface that this field definition is a transient field. Without this the grid won’t show up on the edit form.
widget:columns is a comma-delimited list of the fields in the relationship that you want to be included in the grid.

In the current version, transient fields don’t show up in the add related record form - only the new record and edit record forms. This has been added in SVN and will be included in the next release.

-Steve

jhenry — Wed Jul 15, 2009 4:06 pm

Thanks Steve the grid works great. I think I am doing something wrong though because the work order entry is not pulling in the time entry id and the time entry id is not updating the work order id. There may be an easier way but I needed the ability to have multiple time entries to one work order. Thanks again for a wonderful program.

A related question-When I create the database tables is it necessary to create all the relationships and foriegn keys via sql or by phpadmin or does xataface take care of this?

Thanks

Jason

shannah — Wed Jul 15, 2009 6:02 pm

the work order entry is not pulling in the time entry id and the time entry id is not updating the work order id.

Can you elaborate on this. I’m not sure I fully understand the problem.

When I create the database tables is it necessary to create all the relationships and foriegn keys via sql or by phpadmin or does xataface take care of this?

Xataface works independently of foreign keys, so you don’t need to define them for xataface to work. Future versions may work “smarter” and automatically create relationships based on the defined foreign keys, but i’m not sure if i’ll go that direction or not.

jhenry — Wed Jul 15, 2009 7:48 pm

I have a work order table that has the following columns:

workorder_id int(11) Primary Key

control_number int(11) No Equipment -> control_number

type enum(‘Demand’, ‘PM’) No Demand

contact varchar(40) Yes NULL

problem varchar(60) No

failure_date datetime No

completion_date datetime Yes NULL

employee_id int(5) No Employees -> employee_id

timeentry_id int(11) Yes NULL Time_Entry -> timeentry_id

failuretype_id int(11) No Failure_Type -> failuretype_id

actiontype_id int(11) No Action_Type -> actiontype_id

summary mediumtext Yes NULL

resistance varchar(11) Yes NULL

leakage varchar(15) Yes NULL

auth_name

This table links to the Time_Entry table with fields below:

timeentry_id int(11) Primary Key

employee_id int(11) No Employees -> employee_id

start_date date No

start_time time No

end_time time No

total_time double Yes NULL

workorder_id int(11) No Work_Orders -> workorder_id

When I add a new work order I need to add at least 1 time entry sometimes more. So I need each entry into the time entry to relate to a particular work order. I can access the time entry table from the tabs once I add a work order but I would rather be able to do it from one entry point. Do I really need to link them twice (through the timeentry_id & the workorder_id) or do I need to make sure each time entry is related through the workorder id? Hopefully this gets you the info you need. Sorry if it sounds crazy.

Thanks

Jason

shannah — Wed Jul 15, 2009 7:59 pm

OK. I think your problem is the definition of the relationship in the work orders table. You currently have:

Code: Select all: [Time_Entry] Time_Entry.timeentry_id = "$timeentry_id"

It should be

Code: Select all: [Time_Entry] Time_Entry.workorder_id = "$workorder_id"

And in your table definition I don’t think you need to have a timeentry_id field in the workorders table. Having a workorder_id field in the timeentries table is sufficient to keep them linked.

-Steve

jhenry — Wed Jul 15, 2009 10:50 pm

That worked like a charm thanks for all your help. This is truly an amazing program.

Jason