Re: [dba-users] OOoBase - Example

[Deki Djokic]

Hello Drew, Actually let me ask you this - how would you do it in MS Access - maybe we can find a similar approach in Base? What I would like to do and what I have done with Access is that in frmInspection  value of  fmtWeek .Text was calculated according to value of DateField.Text and inserted in a table. To make it more clear: if user update/change Date, Week number (0-52) has to be automatically calculated and displayed in a DateField. And, yes, I want to use it from the form frmInspection. Hope that I succeed to clarify a topic a bit. Not a problem - you will need to use a scripted macro for this ( unless someone can show me how to do it otherwise ). You would call this macro in the After Record Action event. The macro is not particularly difficult to envision, I am just curious how you will link the two ( or more records ). Looking at the data I see that the field "inspection" is not unique. Is there some combination of fields that will yeild a unique ( natural ) key? Just to explain you 'the name of the game': during construction we do not accept all the things: for such cases we have conclusions 'REJECTED' and 'OWC'. In both cases same item has to be inspected at least one more time. It would be handy to have information which items have to be re-inspected on one place (separate table?). Query of table tInspection with R or OWC as criteria can not help: doesn't mean that same Item is not accepted on some later date. Of course, situation is more complicated, all records in such (new) table has to be unique. Moreover if one re-inspect an item, there is no guarantee that this item will be accepted -  two-three-inspections are not unusual, depending on a Builder. So basically, on insert of new record macro has to check if such record already exist in a  'new'  table: if exist and if conclusion is  R or OWC, macro suppose to update a date in those new table; if conclusion is something else (not R or OWC) macro suppose to delete that record from the new table. If record doesn't exist and conclusion is R or OWC, macro suppose to add record to the table - Date and Inspection are Ok. However, all records has to be 'intact' in a table tInspection. Do you mean the database gets slow at 2500 records? So far (783 records this morning) I haven't came across any degradation in speed and performance. But, yes, I'm afraid that it can happen - let's say 2000 records in tInspection (with memo field), 30-50 in tIssue (with memo field) and constantly 10-30 records in a 'new table' we're talking about. Have a nice day, Deki Andrew Jensen wrote: Deki Djokic wrote: Deki, I was wondering is there anything that you are wanting the database to do that it doesn't? Any enhancements that you are looking to make. Drew, thanks for asking: 1. How to insert function which suppose to calculate week number, according to given date number in frmInspection, txtWeek? I know how to do that with Access, but here... Not completely sure how to answer this first part. OOBasic does not have a function that returns the week of the year for a date variable. HSQL has the built in function WEEK( <date> ) however, which returns the week of the year from 1 - 52. How you would call it depends on how you want to use it. For example do you really need to store this some place or can you simply calculate it from a date field. In you table "tInspection" you have a column "date", you can use the function in a select statement: SELECT *, WEEK( "date" ) AS "week" FROM "tInspection" You can use it update a field with: UPDATE "tInspection" SET "week" = WEEK( '2005-12-28') WHERE "Id_No" = 0 You can also CALL in a SQL statement as in: CALL WEEK( '2005-12-28' ) That all said, I suppose the real problem is how to use one of these from your form? Again, how do you really want to use it. Do you want to store the value, or just use it for display and filtering? Actually let me ask you this - how would you do it in MS Access - maybe we can find a similar approach in Base? 2. If I hit conclusion R or OWC, will be nice feature to record, additionally, Date, Inspection and Conclusion in separate table (eg, tFor_Confirmation). Later, to be removed if new (re-inspection) record is A, QAC, Noted, Witn,... Not a problem - you will need to use a scripted macro for this ( unless someone can show me how to do it otherwise ). You would call this macro in the After Record Action event. The macro is not particularly difficult to envision, I am just curious how you will link the two ( or more records ). Looking at the data I see that the field "inspection" is not unique. Is there some combination of fields that will yeild a unique ( natural ) key? I do not have idea how to do that.  Note one more thing: full table have 1200-2500 records, depends on size of the ship and shi*yard performance: can be time consuming.... I don't understand this question. Do you mean the database gets slow at 2500 records? Drew --