OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Update form field when changing record?
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
SnowmanDK
General User
General User


Joined: 13 Apr 2010
Posts: 10
Location: Denmark

PostPosted: Tue Apr 20, 2010 4:31 am    Post subject: [SOLVED] Update form field when changing record? Reply with quote

I have a database running from a MySQL 3.51 database on an Ubuntu server.

It has a table that contains 2 date fields.
I made a form that also contains those fields (among many others from the table).

What I need it to calculate the difference between the two dates (in days) and result is to appear in a new field.
I made it work through an sql query, but it calculates ALL records in one go, and only updates the FIRST record.
All the other records just show the result from the first one.
It's like this:
Code:
SELECT DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable`

Seems to me I need something that calculates the CURRENT record and then inserts the result into the field, also in the current record.
Anyone have an idea how to make this happen?


Last edited by SnowmanDK on Fri May 28, 2010 1:09 am; edited 2 times in total
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Apr 20, 2010 10:55 am    Post subject: Reply with quote

I'm assuming a SQL expert will chime-in here...and storing a computational result is sometimes a bad idea...
But you will need to add the primary key field to your query and add a WHERE clause that narrows the selection down to the current record (cursor position?).

Code:
SELECT RecordID, DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable` WHERE RecordID = <insert SQL for current record or cursor position>


I always do this with macro code... so if we don't get a SQL solution, I can post the macro code and event trigger that I use.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Tue Apr 20, 2010 11:47 am    Post subject: Re: Update field for each record? Reply with quote

[quote="SnowmanDK"].
It's like this:
Code:
SELECT DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable`

add clause Where. (make reccrental query)
Code:

select  `ToDate`,`FromDate`, (DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable` where `Database`.`tblTable`.`id `=`Database`.`tblTable1`.`id `) from `Database`.`tblTable` AS `tblTable1`
Back to top
View user's profile Send private message
SnowmanDK
General User
General User


Joined: 13 Apr 2010
Posts: 10
Location: Denmark

PostPosted: Wed Apr 21, 2010 1:51 am    Post subject: Reply with quote

I am not sure if I forgot to point it out, but I don't want to store the results.
Just show them in the form for the current record.

BigAndy:
When I try to use your query, then I get an error, saying that the syntax is wrong.
Code:

1: [MySQL][ODBC 3.51 Driver][mysqld-5.1.37-1ubuntu5.1]You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable` WHERE `Database' at line 1

Your example contains the table tblTable1 and the field id.
Where do they come from?


dacm:
That macro code you refer to... If you have it ready, then I would be very interested in looking at it. I might be able to use it later Wink
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Apr 21, 2010 8:30 am    Post subject: Reply with quote

SnowmanDK wrote:
That macro code you refer to... If you have it ready, then I would be very interested in looking at it. I might be able to use it later Wink

Well I would use the macro to determine the current record and issue a more simple the SQL query separately using the current record info provided as a variable. So the simple SQL query would avoid the record ID issue that's causing your problem at the moment. But I would only do that if it were driven by my design philosophy. That's based on avoiding network "traffic/throughput" first-and-foremost...and then avoiding "server-side processing" whenever possible. In theory, it maintains application response with more concurrent users. But in this case, I don't see any benefit to a macro.

So I would create a subform with Form: Data > Content type > SQL command.

Enter the following code in Form: Data > Content:

HSQLDB:
Code:
SELECT "tableID", DATEDIFF( 'Day', "FromDate", "ToDate") AS "DaysTotal" FROM "tblTable" AS "tblTable"


MySQL (based on your posted code syntax):
Code:
SELECT `tableID`, DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable`


Then Form: Data > Link master/mlave fields > tableID.

Then add a Formatted Field control to the new subform perhaps using Form Navigator to drag&drop it to the subform as necessary.

Then Control > Properties: Data > Data Field > DaysTotal

That way, the subform field can't see anything but the DaysTotal from the current record tableID. And I know this all works with HSQLDB because I just made a mockup.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
SnowmanDK
General User
General User


Joined: 13 Apr 2010
Posts: 10
Location: Denmark

PostPosted: Fri Apr 23, 2010 1:29 am    Post subject: Reply with quote

dacm wrote:

MySQL (based on your posted code syntax):
Code:
SELECT `tableID`, DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable`


Then Form: Data > Link master/slave fields > tableID.

Then add a Formatted Field control to the new subform perhaps using Form Navigator to drag&drop it to the subform as necessary.

Then Control > Properties: Data > Data Field > DaysTotal

That way, the subform field can't see anything but the DaysTotal from the current record tableID. And I know this all works with HSQLDB because I just made a mockup.

Problem is, it doesn't seem to work in MySQL. It updates the first record (as initial code did), but nothing else.

BigAndy wrote:

Code:
select  `ToDate`,`FromDate`, (DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable` where `Database`.`tblTable`.`id `=`Database`.`tblTable1`.`id `) from `Database`.`tblTable` AS `tblTable1`

When I try the above code, then I am told it's the wrong syntax. The part of it below does not fail though, but ends up with the same results as the other attempts. Only first record is updated.
Code:
select  `ToDate`,`FromDate`, (DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal` FROM `Database`.`tblTable` AS `tblTable` where `Database`.`tblTable`.`id `=`Database`.`tblTable1`.`id `)


I am beginning to suspect that a macro is the only way out...
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Apr 23, 2010 2:12 am    Post subject: Reply with quote

Hello

I think the OP must before he start with macro learn how forms are working. It seemes to me he did not understand how forms are working.
How have the OP poster organize his form?.

It seemes to me that the both other person use subforms for displaying the wanted result.

a) Does the OP have a subform
b) What are the master / slave fields

Romke
Back to top
View user's profile Send private message
SnowmanDK
General User
General User


Joined: 13 Apr 2010
Posts: 10
Location: Denmark

PostPosted: Fri Apr 23, 2010 2:25 am    Post subject: Reply with quote

RPG wrote:
Hello
a) Does the OP have a subform
b) What are the master / slave fields
Romke

I do use both a subform for the query and also the master/slave fields Wink
But it runs the query once, where it calculates ALL records and adds the results to a "temporary table" (don't know what else to call it). Problem is, no matter what record I go to, it only shows the result for the first record.

Maybe it's a limitation in MySQL?

I also know a little about macro programming (made some for Calc and Writer).
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Apr 23, 2010 3:05 am    Post subject: Reply with quote

Hello

Quote:
But it runs the query once, where it calculates ALL records and adds the results to a "temporary table" (don't know what else to call it). Problem is, no matter what record I go to, it only shows the result for the first record.

This makes that I do think the relation between select record and the wanted result is not good. You have to make them in the form design.

Quote:
Maybe it's a limitation in MySQL?
I cannot give an answer on that question there I don't use MySQL.


When you want use macros then you have to write them all. Recorded macros don't work for OOo-base.


Romke
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Apr 23, 2010 5:06 pm    Post subject: Reply with quote

SnowmanDK wrote:
I do use both a subform for the query and also the master/slave fields Wink
But it runs the query once, where it calculates ALL records and adds the results to a "temporary table" (don't know what else to call it). Problem is, no matter what record I go to, it only shows the result for the first record.

Maybe it's a limitation in MySQL?

If you've tested the SQL query and can verify that the correct table and values are generated, then it's not a MySQL issue.

If you're saying you've got a subform linked master/slave using: tableID. ...Then the only way to get the wrong DaysTotal value would be if the Formatted Field is located in the main-form rather than in the subform. Since it's all the same form visually, the only way to determine the location of a control with certainty is to use the Form Navigator tree-view. Can you verify the location of your DaysTotal-Formatted Field control?
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
SnowmanDK
General User
General User


Joined: 13 Apr 2010
Posts: 10
Location: Denmark

PostPosted: Tue Apr 27, 2010 4:38 am    Post subject: Reply with quote

dacm wrote:
Can you verify the location of your DaysTotal-Formatted Field control?

Yes! It is placed in my subform Wink

My problem is that I can run the following query, which gives me all the results at once for all records:
Code:
SELECT `CaseID`, `ToDate`, `FromDate`,
DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal`
FROM `Database`.`tblTable` AS `tblTable`

But when I add the following "WHERE" clause, I get an error saying that column "ID" is unknown:
Code:
WHERE `database`.`tblTable`.`CaseID` = `ID`

I need something that says something like
Code:
WHERE `database`.`tblTable`.`CaseID` = `CURRENTRECORD`.`ID`

but how can I get the ID of the currently shown/active record?
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Apr 27, 2010 1:48 pm    Post subject: Reply with quote

Hello

I have read again the thread.
I want tell you that it is not possible to update, as the title tells, records in a form.

Romke
Back to top
View user's profile Send private message
SnowmanDK
General User
General User


Joined: 13 Apr 2010
Posts: 10
Location: Denmark

PostPosted: Tue Apr 27, 2010 10:07 pm    Post subject: Reply with quote

I am sorry if I made people misunderstand me, or I didn't explain properly Embarassed
The "update field" part of the title is misleading, I see now.
I was actually refering to a formatted field in my form.

Hope the info below clarifies everything Embarassed :

I have a form that contains 2 datefields (FromDate and ToDate) and a subform.
The subform contains a formatted field (DaysTotal).
The datefields gets their content from a mysql db running from a server (not local).
I connect to the db through the mysql v3.51 connector.

I want the DaysTotal to show how many days there is between FromDate and ToDate.
No data is to be stored anywhere.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed Apr 28, 2010 9:40 am    Post subject: Reply with quote

Hello

I think you have to learn better how forms are working.
As far as I understand your problem there is no problem with the queries for main form and sub form. I think the only problem is the relation main form to subform.

You must have a main form with I think this a query:
SELECT
`ToDate`,
`FromDate`,
FROM `Database`.`tblTable` AS `tblTable`


Your must have a subform with I think this query.
SELECT
`CaseID`,
DATEDIFF(`ToDate`,`FromDate`) AS `DaysTotal`
FROM `Database`.`tblTable` AS `tblTable`

The relation in your subform and main form is for masterfield and slavefield both : CaseID

When you need a selection on the table then talk later about it. Better then working to design your own form with the relation learn how the form wizards makes it for you.

Romke
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Apr 30, 2010 9:34 am    Post subject: Reply with quote

A few different ways to implement this...



The same SQL command is used in all cases (not the MySQL specific syntax).
Notice there's no WHERE clause input...



And here's the file if you're interested in examining the HSQLDB-embedded version in detail.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group