| View previous topic :: View next topic |
| Author |
Message |
SnowmanDK
General User
Joined: 13 Apr 2010
Posts: 10
Location: Denmark
|
 Posted: Tue Apr 20, 2010 4:31 am Post subject: [SOLVED] Update form field when changing record? |
 |
|
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 |
|
 |
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
| Posted: Tue Apr 20, 2010 10:55 am Post subject: |
|
|
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 + Migrating 'Embedded databases' |
|
| Back to top |
|
|
BigAndy
OOo Enthusiast
Joined: 03 Jan 2010
Posts: 150
|
| Posted: Tue Apr 20, 2010 11:47 am Post subject: Re: Update field for each record? |
|
|
[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 |
|
|
SnowmanDK
General User
Joined: 13 Apr 2010
Posts: 10
Location: Denmark
|
| Posted: Wed Apr 21, 2010 1:51 am Post subject: |
|
|
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  |
|
| Back to top |
|
|
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
| Posted: Wed Apr 21, 2010 8:30 am Post subject: |
|
|
| 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 |
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 + Migrating 'Embedded databases' |
|
| Back to top |
|
|
SnowmanDK
General User
Joined: 13 Apr 2010
Posts: 10
Location: Denmark
|
| Posted: Fri Apr 23, 2010 1:29 am Post subject: |
|
|
| 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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Fri Apr 23, 2010 2:12 am Post subject: |
|
|
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 |
|
|
SnowmanDK
General User
Joined: 13 Apr 2010
Posts: 10
Location: Denmark
|
| Posted: Fri Apr 23, 2010 2:25 am Post subject: |
|
|
| 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
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Fri Apr 23, 2010 3:05 am Post subject: |
|
|
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 |
|
|
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
| Posted: Fri Apr 23, 2010 5:06 pm Post subject: |
|
|
| SnowmanDK wrote: | I do use both a subform for the query and also the master/slave fields
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 + Migrating 'Embedded databases' |
|
| Back to top |
|
|
SnowmanDK
General User
Joined: 13 Apr 2010
Posts: 10
Location: Denmark
|
| Posted: Tue Apr 27, 2010 4:38 am Post subject: |
|
|
| dacm wrote: | | Can you verify the location of your DaysTotal-Formatted Field control? |
Yes! It is placed in my subform
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Tue Apr 27, 2010 1:48 pm Post subject: |
|
|
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 |
|
|
SnowmanDK
General User
Joined: 13 Apr 2010
Posts: 10
Location: Denmark
|
| Posted: Tue Apr 27, 2010 10:07 pm Post subject: |
|
|
I am sorry if I made people misunderstand me, or I didn't explain properly 
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 :
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Wed Apr 28, 2010 9:40 am Post subject: |
|
|
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 |
|
|
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
|
| Back to top |
|
|
|
