Page 2 of 2
Re: Is OO Base good for this?
Posted: Tue Jan 22, 2019 5:31 pm
by UnklDonald418
Go to Tools>Relationships and delete the relation between TBL_DOSSIER and TBL_DOSSIER_INT_RECIPE and save the results.
Edit TBL_DOSSIER and change the Auto Value to Yes for "DOSSIER_ID".
Go back to Tools>Relationships and reconnect the relation between TBL_DOSSIER and TBL_DOSSIER_INT_RECIPE and save the results.
That should eliminate your error message.
Re: Is OO Base good for this?
Posted: Wed Jan 23, 2019 4:47 pm
by Plomo
thank you Donald, it indeed eliminated the error message.
I did some testing and the outcomes are quite confusing if not terrifying to me. Here we go:
At first glance, hovering the mouse on the form i noticed this:
all the fields are fillable except for
SIlkscreen,
Exposure,
Series, all the Inks and all the
Adds.
Pantone combo-box,
Gr fields and
% fields: i can click on them but i can't write anything.
I guess the explanation is: before filling the sub-form i need to fill the main form fields.
So i filled the first 4 fields and then i noticed this:
only clicking on
Pantone combo-box or
Gr or
% fields "activate" all the remaining fields
Also, If i close the form after filling some or all the fields till
Pantone 1 it asks me if i want to save the record. If i fill on more, ti doesn't ask me anymore, it just close the window.
Is it normal? Can i make so that it ask me if i want to save every time i add or modify anything?
Every time i choose a Pantone in the
Pantone combo-box it replace it with a
zero as soon as i click somewhere else, while writing a brand new Pantone this error message
...pops up every time i click somewhere in the forum (still, it allows me to keep filling the fields)
When i choose a
Ink it automatically adds all the
Additives (with the corresponding number, 1, 2 or 3)
If i choose an
Additive it automatically add all the remaining additives and the
Ink 1.
If i add a new record it (of course) empty all the fields but if i fill one, lets say the Client, and then i save the record it brings back the last saved data in the sub-form…
Also, the TBL_DOSSIER_INT_RECIPE is still empty.
It looks like there's actually no tables bounded to it...
Maybe there's something wrong with the Form/subform structures? cause TBL_RECIPES is done pretty much like the TBL_DOSSIER and this last one seems to work well..
Cheers to everyone wherever you are!
Re: Is OO Base good for this?
Posted: Wed Jan 23, 2019 5:21 pm
by Villeroy
The error about referencial integrity says that you are trying to add some ID_PANTONE (foreign key) that does not exist as a primary key in the pantone table.
You have pantones 0,2,4,6 in the pantone table and somehow you try to add ID_PANTONE 3 to TBL_RECEIPES.
There is exactly ONE way to edit many-to-many relations in Base forms.
See Postby Villeroy » 18 Jan 2019, 17:16 in this thread
Re: Is OO Base good for this?
Posted: Wed Jan 23, 2019 5:55 pm
by Plomo
Villeroy wrote:There is exactly ONE way to edit many-to-many relations in Base forms.
So you're saying that's the ONLY ONE way to do it? I was hoping to do it with all the list boxes detached, being free to adjust the layout of the form as i wanted.
Well, i surrender and, assuming the tables and their relationships are all ok, i'll try that ONE way to create the form.
Thank you very very much for you help.
Cheers.
Re: Is OO Base good for this?
Posted: Wed Jan 23, 2019 7:12 pm
by Villeroy
This is the ONLY ONE way.
------
First table on the main form.
Intermediate table on a subform's grid.
Second table in a column of list boxes within the subform's grid.
----------
Now you can select one record on the main form and see all related items of the other table in the subform grid where you can change, remove and add new items.
Re: Is OO Base good for this?
Posted: Wed Jan 23, 2019 10:45 pm
by UnklDonald418
I guess the explanation is: before filling the sub-form i need to fill the main form fields.
Yes, since a SubForm needs to be related to the MainForm via the
Link master fields and the
Link slave fields properties, the MainForm record must exist before anything on the SubForm can be related to it.
Based on the recent version you uploaded, when I open
TESTING FORM in the Edit/Desgn Mode and look at the Data page of the
Form Properties dialog for
SUB-FORM RECIPE, I see there are no values in the
Link Master fields and
Link Slave fields, which means the MainForm and SubForm are working independently of each other, explaining the odd behavior you are seeing.
When I try to select values for the
Link Master fields and
Link Slave fields I find there are no fields that could be used for this. That is where the intersection table comes into the picture.
Before making any changes, make a copy of
TESTING FORM so you can easily return to the starting point if needed.
Add a new SubForm to MainForm and use
TBL_DOSSIER_INT_RECIPE as the
Content property, then you can use "DOSSIER_ID" for
Link Master fields and "DossierID" as the
Link Slave fields. For now, add a table control to the new SubForm so you can display/edit the 3 fields in that table.
Using the
Form Navigator drag
SUB-FORM RECIPE so that it becomes a SubForm under the newly added SubForm, making it a SubSubForm . Then you can use "RecipeID" for the
Link Master fields and "ID_RECIPE" as the
Link slave fields in
SUB-FORM RECIPE.
This brings up a point that needs to be cleared up before going much further. The only way we can uniquely identity a particular recipe is by "ID_RECIPE". If you plan on re-using recipes (a many to many relationship between TBL_DOSSIER and TBL_RECIPES) the user would need to know "ID_RECIPE" number, which may not be realistic.
First question, do you actually need to reuse the recipes?
If so, is there more user friendly way to differentiate the recipes than the value of "ID_RECIPE"?
Unlike list boxes, Combo boxes do not have a boundfield. They store what they display so they are typically used with text fields.
To enter a new Pantone into
TBL_Pantoni_List will require a separate form. It could be incorporated in
TESTING FORM, but would need a separate MainForm.
Re: Is OO Base good for this?
Posted: Thu Jan 24, 2019 12:42 pm
by Plomo
Thank you guys.
Hi Donald, let's clear that point up.
UnklDonald418 wrote:First question, do you actually need to reuse the recipes?
The main reasons i need this database for are:
- To compile, modifying, consulting and store all the Dossiers (with all the datas related, recipes included) in one place.
- To be able to answer the question: "Have we already printed this Pantone on this kind of packaging?" and then, eventually, print the corresponding recipe/recipes for the operator's use. Because creating recipes from scratch is time consuming, replicate old recipes or at least use them as a starting point would be a huge advantage. Also, comparing all the recipes of the same Pantone allows me to find differences and investigate the reasons behind them (as i said, printing the exact same Pantone on the exact same packaging on the exact same machine doesn't mean the recipe will be exactly the same).
So the answer is: "yes and no".
Yes because i need to re-use the recipes as reference for new productions.
No because, during the production, that new recipe could remain the same as the one used for reference or change in some/all parameters. Either ways that new recipe is going to be linked to its own Dossier (if it remains the same as the one used for reference it's not necessary to have it stored in the database (having so duplicates) but it has to be displayed within its Dossier, available by the form).
I'm now thinking, is a
many-to-many relationship what i really need here? Or is a
many(recipes)
-to- one(dossier) the way to go?
UnklDonald418 wrote:is there more user friendly way to differentiate the recipes than the value of "ID_RECIPE"?
The data i use to search through recipes is the
name of the Pantone and second to that the
kind of packaging (
Pantone 1 printed on a white bottle will surely have a different recipe if printed on a black bottle). But as you can understand, the name of the Pantone can't be the way to uniquely identify a particular recipe.
Now that i think again, i see your first question under a different light and maybe the answer to it should be "
no". Of course it would be faster to resume an already written recipe instead to write it again but there's not certainty that the operators will not have to change something creating then a different recipe. Consequently when i'm getting back the filled-in-by-hand Dossier i may have to change the recipe i wrote in the form BUT it doesn't mean the recipe i used is now different, it remains the same as it was in the previous production and now i have a new (slightly/completely different) recipe. To make it possible i guess it should have been a new recipe from the beginning, am i right? And this would justify a "no" as an answer to your first question 'cause on every new dossier i'll have a new recipe even if identical to old ones...
And now i feel like this:
Re: Is OO Base good for this?
Posted: Thu Jan 24, 2019 7:50 pm
by RoryOF
Plomo wrote:The data i use to search through recipes is the name of the Pantone and second to that the kind of packaging (Pantone 1 printed on a white bottle will surely have a different recipe if printed on a black bottle). But as you can understand, the name of the Pantone can't be the way to uniquely identify a particular recipe
I recollect only two sets of Pantone samples - coated and uncoated: Surely, on a black bottle you ought use an appropriate undercoat, or else fully opaque inks. After all, silk screen can print white on black.
Re: Is OO Base good for this?
Posted: Thu Jan 24, 2019 9:22 pm
by UnklDonald418
Database design is not easy.. A designer hopes to get it right the first time, but false starts do happen, especially for beginners.
The more I looked at where you seemed to be headed the more it looked like a many to many relationship might cause more problems than it would solve.
Yes, using a one to many relationship between TBL_RECIPES and TBL_DOSSIER will likely result in duplicates being stored in TBL_RECIPES, but that is just one of those trade-offs.
Add a foreign key column for "DossierID" to TBL_RECIPES and then you can directly link TBL_RECIPES on the SubForm and TBL_DOSSIER on the MainForm.
Yes, "ID_RECIPE" is the only unique identifier, but you could use a FILTER table on a form that would allow the user to select a pantone name in one list box and a type of packaging in another list box and use those selections in a query to generate a list of matching recipes.
Re: Is OO Base good for this?
Posted: Fri Jan 25, 2019 11:11 am
by Plomo
Hi Donald,
let's go with the one-to-many path.
Adjusting the relationships i thought: aren't the relationships between TBL_RECIPES and Inks and Additives also of the one-to-many kind? At the end, one recipe can have many inks and additives. So i could just add a FK to those two tables, link them to the PK on TBL_RECIPES and get rid of ink/additives 1,2,3,4,5,6 field.. right? Like this:
Now i wonder if there's a way to connect
Grams to the Inks and
Percentages to Additives. For now it's not a "priority", they just need to be displayed next to each other but in the future it would be cool to monitor, in a storage-management perspective, how much of "that ink" has been used or what are the most inks used...
Thanks everybody and have a nice week-end!
Re: Is OO Base good for this?
Posted: Sat Jan 26, 2019 7:39 pm
by UnklDonald418
With "Ink1" … "Ink6", "Gr1" … "Gr6", "Add1" … "Add6", "Perc1" … "Perc6" in
TBL_RECIPES, to find
how much of "that ink" has been used or what are the most inks used
would require a minimum of 6 queries connected by UNION operators, but if
TBL_RECIPES is normalized that could be accomplished with a single query. Unless there are always 6 of each of those values in each recipe, normalization will also result in more efficient data storage.
TBL_Inks and
TBL_ADDITIVES are merely lists of available values that can be used by a list box, so they wouldn't work the way you show on your recent relationship diagram. Leave them they way they were originally configured and assuming there is a one to one relationship between "Ink1" and "Additive1", then create a new table something like
Code: Select all
CREATE TABLE "TBL_RecipeDetail" (
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
"RecipeID" INTEGER,
"InkID" INTEGER,
"Gr" INTEGER,
"AdditiveID" INTEGER,
"Perc" INTEGER,
CONSTRAINT FK_RECDET FOREIGN KEY ("RecipeID") REFERENCES "TBL_RECIPES" ("ID_RECIPE"),
CONSTRAINT FK_INK FOREIGN KEY ("InkID") REFERENCES "TBL_Inks" ("INKS_ID"),
CONSTRAINT FK_ADD FOREIGN KEY ("AdditiveID") REFERENCES "TBL_Additivs" ("ADDITIVES_ID")
);
The new table has a place for the "Ink" and "Gr" used, along an "Additive" and "Perc" concentration. The table also has "RecipeID" used to link a row in this table to a specific recipe. The result is the needed one to many relationship between
TBL_RECIPES and the new
TBL_RecipeDetail.
One trade-off is that
TESTING FORM will need significant redesign.
Re: Is OO Base good for this?
Posted: Mon Jan 28, 2019 12:25 pm
by Plomo
Hi Donald, i hope you had a great week end.
I successfully executed your code creating the new TBL_RecipeDetail (see screenshot). It's not really clear to me if i have to connect again TBL_Inks and TBL_Additives to the fields Ink1..6 and Add1..6 in TBL_RECIPES.
UnklDonald418 wrote:but if TBL_RECIPES is normalized
After correctly connecting Inks and Additives do you think there's more to do to normalize TBL_RECIPES?
UnklDonald418 wrote:Unless there are always 6 of each of those values in each recipe
The simplest Pantone recipe will contain 100
gr of only one
Ink (it would be Ink1, just cause using the first slot is the most organic choice) and
no additives at all.
All the other cases are made adding up to 6 maximum Inks and up to maximum 4 additives
*.
A Dossier will have at least 1 and maximum 6 Pantone recipes
*(we tend to create recipes distributing inks grams on a total of 100 grams for easier calculation... but it's not always possible so it's impossible to establish a precise maximum amount of grams if there are 2 or more inks in a recipe. The amount of a single additive will be 0,1% up to 20% on the total inks grams)
UnklDonald418 wrote:One trade-off is that TESTING FORM will need significant redesign.
I'm ok with trades-off as long as in the DOSSIER/TESTING FORM the recipe-details of all the possible Pantoni (whether they are 1 or 6) are
always visible without having to interact with the form so i can print a blank Dossier for the operators and a filled Dossier for the archive ...
wait a second: what if i create 6 forms, one with only 1 recipe-details displayed, one with only 2, one with only 3 etc etc... Would it help somehow? I don't know, now that i think better it seems just pointless but i thought to ask anyway
.
Re: Is OO Base good for this?
Posted: Mon Jan 28, 2019 7:53 pm
by UnklDonald418
It's not really clear to me if i have to connect again TBL_Inks and TBL_Additives to the fields Ink1..6 and Add1..6 in TBL_RECIPES.
Based on your relationship diagram, since "Ink", "Gr", "Add" and "Perc" appear in
TBL_RecipeDetail they should be removed from
TBL_RECIPES.
After correctly connecting Inks and Additives do you think there's more to do to normalize TBL_RECIPES?
Once the fields mentioned above are removed,
TBL_RECIPES appears to be fully normalized, at least for now.
you also said
amount of a single additive will be 0,1% up to 20%
In
TBL_RecipeDetail, "Perc" is defined as an Integer, so that needs to be changed. If you are not doing any calculations in the database with "Perc", then Decimal(5,1) would work, otherwise to be absolutely correct "Perc" should be Decimal (6,3) and then use a Formatted Field control on a form to convert it to %
A Dossier will have at least 1 and maximum 6 Pantone recipes
implies that the one to one relationship I assumed between "Inks" and "Additives" doesn't exist. That means
TBL_RecipeDetail could be replaced with two tables, one for "Ink" and "Gr" plus another for "Additive" and "Perc".
The question becomes, would the efficiency gains be worth the added complexity?
the DOSSIER/TESTING FORM the recipe-details of all the possible Pantoni (whether they are 1 or 6) are always visible without having to interact with the form
With the normalized tables it would be difficult to display everything on a single Base form.
what if i create 6 forms, one with only 1 recipe-details displayed, one with only 2, one with only 3 etc etc...
At this point I'm not sure how to make that that work.
But Reports are one solution
Another option would be to use the database tables and queries as a data source for a spreadsheet and print the spreadsheet.
can print a blank Dossier for the operators
If the operators are going to be entering data onto a blank form and not directly into the database, then your original pdf should work for that.
a filled Dossier for the archive
With all the data stored in the database, it would not be difficult to create a Base form for retrieving the archival data, so saving a copy may not be necessary.
Re: Is OO Base good for this?
Posted: Tue Jan 29, 2019 4:31 pm
by Plomo
Hi Donald,
thank you again for your support.
UnklDonald418 wrote:they should be removed from TBL_RECIPES.
Of course, i recognize i made i dumb question
Done, removed. About Perc decimal: calculating the percentage would result in such small numbers that i don't think it's gonna be any useful so no, it can be just a decimal.
UnklDonald418 wrote:the one to one relationship I assumed between "Inks" and "Additives" doesn't exist.
I'm sorry i think i misunderstood you, i thought you were talking about the 1-to-1 relations between "inks and recipe" and "additives and recipe"
UnklDonald418 wrote:would the efficiency gains be worth the added complexity?
My inexperienced eyes can't see any efficiency worth the complexity. Maybe it would be just more "theoretically" correct?
UnklDonald418 wrote:With the normalized tables it would be difficult to display everything on a single Base form.
So if i want things to be displayed in my way... the database shouldn't be well structured
UnklDonald418 wrote:Another option would be to use the database tables and queries as a data source for a spreadsheet and print the spreadsheet.
That's interesting. How does it work? I'm picturing it this way: i'll have a spreadsheet with the pdf layout with all the blank fields visible and with list-boxes linked to the database tables such as TBL_Clients, TBL_Packagings, TBL_Rubbers, TBL_Series... (those with "one-to-many" relationships). Am i right? But, the data i put into the spreadsheet will fill the database (using the spreadsheet like a kind of
form) or it's a one-way interaction (from database to spreadsheet only)? ...or you mean: i still use the database form, even if not displayed as i want, to fill the database but what i print is a spreadsheet automatically
* filled up through tables and queries generated by the form?
*(It has to be automatic, having to write the same datas twice voids everything)
UnklDonald418 wrote:If the operators are going to be entering data onto a blank form and not directly into the database
Yep, they will lay a hand only on printed forms, empty or partially/completely filled (for adjustments). They will not have access to the database.
UnklDonald418 wrote:so saving a copy may not be necessary
I see. I meant a "printed" copy for the archive (needed when a product has to be printed again or to be consulted by operators)
Re: Is OO Base good for this?
Posted: Tue Jan 29, 2019 4:56 pm
by Villeroy
A report is printable database output. To print a paper sheet about some single item create a report based on query like this:
Code: Select all
SELECT * FROM "somewhere" WHERE "ID" = :Please_Enter_Item_Key
Every time you run the report, you will be prompted for the item key. If you put the key into the clipboard before running the repord, you only need to paste, hit OK, wait a moment and print.
Re: Is OO Base good for this?
Posted: Wed Jan 30, 2019 1:05 am
by UnklDonald418
My inexperienced eyes can't see any efficiency worth the complexity. Maybe it would be just more "theoretically" correct?
I tried splitting
TBL_RecipeDetail into two files and did not see how it would improve anything, so I agree with staying with the current
TBL_RecipeDetail.
On a new Calc Sheet press F4 to open the
Data Sources dialog. There you can see your registered databases with their associated tables and queries. Select a table or query in the left side of the dialog and the data will be displayed in the right side. You can drag rows of data or even entire tables from the dialog and display the data on a spreadsheet. However, any changes made on the spreadsheet do not affect what is stored in the database tables, data entry must still be done in Base. While it is possible to import data from a spreadsheet into a database table, spreadsheets have no concept of relations, limiting the utility of those imports.
You will need to create queries to display most of data in your database.
Re: Is OO Base good for this?
Posted: Mon Feb 04, 2019 10:00 am
by Plomo
Hi Donald,
considering your last explanation about Calc Sheet and after a few testing i don't think it would be useful to my purpose. But it gave me a new idea (thanks to Villeroy as well). I'm going to expose it so you may tell me if it could work or not.
It's pretty simple so i wonder if we actually already talked about it, if so: i'm sorry in advance and i blame my bad memory
The entire idea is based on the following fact:
- with Form i will never be able to get a fillable and printable dossier with the layout i want.
- i will, instead, be able to achieve that with Queries and or Reports.
So:
I print the PDF file with the layout i want and i give it to the operators. They will hand fill it and give it back to me. I'll copy the data in a Form: it can have whatever layout it has to have, a simple drop-down list of fields or tables/grids like we talked about. Whenever i have to consult or print the completed new Dossier (or an old one for reference) i will not do it with the Form (which i'll use only for input and adjustment) but with a Report that resemble the PDF layout created by queries related to the tables.
Please let me know if it makes any sense to you and if it could work this way. If that's the case, the big step will be learn how to use queries (i already have an idea) and reports (no clue
).
Cheers to everybody and thanks again for your significant support.
Plomo
Re: Is OO Base good for this?
Posted: Tue Feb 05, 2019 7:35 pm
by UnklDonald418
I put together a demonstration that shows how a report (
ReportQ10R) could be used to display the data stored in the database.
I am including 2 .odb files because combined they are too large to upload here. Open them both and copy
ReportQ10R from
DBCartellaDemoReport.odb to
DBCartellaDemo.odb. Then you can discard
DBCartellaDemoReport.odb.
A report can have only one data source and since the data needed resides in multiple tables a query joining the tables is required.
As currently designed, the value stored in "CL_ID" is the basis for entering and locating specific records in the database.
The demonstration includes 2 forms.
CartEnt is a form for editing existing and adding new data using a MainForm/SubForm//SubSubForm configuration with "CL_ID" as the primary reference.
CartFILTERSel uses a FILTER table which is a special type of table that can have multiple columns but always has only one row. On
CartFILTERSel is a listbox control where the user can select a specific client and the "CLIENTI_ID" value is stored in the "F2" column of FILTER. When the user presses the Display button the SubForm uses the value stored in the "F2" column of FILTER to generate a list of all "CL_ID" values assigned to the selected client. The list is displayed on the SubForm table control, sorted with most highest "CL_ID" value appearing first.
Below the table control is a formatted field control where the user can type a CL_ID value that will be stored in column "F1" of FILTER. Now a query can be created using that value to limit the output to that single "CL_ID"
Query10R was originally created using the GUI,
Create Query in Design View. The Base GUI generates a query in SQL (the language of HSQLDB) with implicit joins for all the tables. While perfectly valid, the SQL generated can be a challenge to read. I included a second version Query10RE using the
SQL Direct mode with explicit joins making it slightly more readable. Both versions should deliver the same results. When I executed them I noticed that there were records in "TBL_RicetteDet" missing from the results. That was caused by
The simplest Pantone recipe will contain 100 gr of only one Ink (it would be Ink1, just cause using the first slot is the most organic choice) and no additives at all.
which means that "TBL_RicetteDet"."ADDITIVI_ID" can store Null values. When
Code: Select all
JOIN "TBL_Additivi" ON "TBL_Additivi"."ADDITIVI_ID" = "TBL_RicetteDet"."ADDITIVI_ID",
encountered a Null value there were no matching record in "TBL_Additivi"."ADDITIVI_ID", so the row was skipped. SQL does provide a COALESCE function that can substitute a legal value for a Null. I decided to use a little trick and modified "TBL_Additivi" with a string 'zzzz' in "TBL_Additivi"."Additivo" for "ADDITIVI_ID" value of 0. Then
Code: Select all
JOIN "TBL_Additivi" ON "TBL_Additivi"."ADDITIVI_ID" = COALESCE ( "TBL_RicetteDet"."ADDITIVI_ID", 0 )
will substitute 0 for null values, and the missing rows are now included in the results. When the listbox query runs, the string 'zzzz' will appear as the last entry in the list.
But I don't really want to see zzzz on the report so I included
Code: Select all
CASEWHEN( "TBL_RicetteDet"."ADDITIVI_ID" >= 0,"TBL_Additivi"."Additivo", '--' ) AS "Additivo"
which causes the query to display a pair of dashes '–' whenever "TBL_RicetteDet"."ADDITIVI_ID" is Null or 0.
Re: Is OO Base good for this?
Posted: Wed Feb 06, 2019 12:13 am
by UnklDonald418
After posting my last message it occurred to me that most of the changes noted in my last message, including the COALESCE and CASEWHEN statements would not be needed if the tables were changed slightly.
I replaced the string zzzz with a single space character in TBL_Additivi
I replaced all the NULL values with 0 in "TBL_RicetteDet" then added NOT NULL constraints and set the default values to 0 by executing
Code: Select all
ALTER TABLE "TBL_RicetteDet" ALTER COLUMN "ADDITIVI_ID" SET NOT NULL;
ALTER TABLE "TBL_RicetteDet" ALTER COLUMN "Perc" SET NOT NULL;
ALTER TABLE "TBL_RicetteDet" ALTER COLUMN "ADDITIVI_ID" SET DEFAULT 0;
ALTER TABLE "TBL_RicetteDet" ALTER COLUMN "Perc" SET DEFAULT 0;
at Tools>SQL
A quick edit of CartEnt to be sure the default values for the two affected columns were set on the table control.
No other changes are required, so copy the report from the previous message into this version of the database and try the report and forms.
Re: Is OO Base good for this?
Posted: Mon Feb 11, 2019 4:45 am
by UnklDonald418
On the report in the demonstration I uploaded, the recipe numbers display the value from "TBL_RICETTA"."ID_RICETTA". Those values are not easily predicted and not necessarily consecutive. To get the report to display Recipe 1, Recipe 2 etc. requires adding a sequence number which can be generated using the SQL function ROWNUM() in a query. Something like
Code: Select all
SELECT "TBL_Cartella"."CL_ID",
"TBL_RICETTA"."ID_RICETTA",
ROWNUM( ) AS "RN"
FROM "TBL_RICETTA"
JOIN "TBL_Cartella" ON "TBL_RICETTA"."CartellaID" = "TBL_Cartella"."CL_ID"
JOIN "FILTER" ON "TBL_Cartella"."CL_ID" = "FILTER"."F1"
Using this as a sub-query in the report query makes the
RN value available to display with each recipe on the report.
However, the ROWNUM() function isn't available in the HSQLDB version 1.8 used by an
Embedded database. That function is found in HSQLDB beginning with version 2.3.2 so I moved the demonstration database to a split database with a
JDBC connection. In a
JDBC database the tables are stored externally, so there is a directory with sub-directories devoted to the database. It is much to large to upload to the forum so I packed the whole thing in a zip archive and uploaded it to
https://www.mediafire.com/file/6dz6o2bi ... t.zip/file
Download and unzip the archive, it should create a directory named
DB_CartelleDemoSplit. In that directory you should find
DB_CartelleDemoSplit.odb and 2 sub-directories.
The
database sub-directory contains the tables. The
driver sub-directory contains a single file,
hsqldb.jar which is the database engine, In this case version 2.3.4. You could replace that file with a more recent version (currently 2.4.1), but some Base users have some reported compatibility issues, beginning with version 2.4.0. Since version 2.3.4 has the needed function I see no compelling reason to use a more recent version.
When you open
DB_CartelleDemoSplit.odb you might get a warning that this file contains macro code. A split database requires a macro to set up the connection to the externally stored tables, so be sure to select the
Enable Macros option.
Run the report and you should see the recipes numbered from 1 to 6 or whatever the number is of the last recipe entered for that
CL_ID value
That scheme also allows creating a group of 6 similar queries, each returning a single recipe. They make possible the new form,
CartellaDisp. Note that the data on that form is read-only, so any edits or entry must still be done using
CartEnt.
CartellaDisp does have a listbox that can be used to select a
CL_ID value and display up to 6 related recipes on a single page.
Let me know if you have any problems or questions.
Re: Is OO Base good for this?
Posted: Mon Feb 11, 2019 6:29 pm
by Plomo
Hi Donald,
again, I'm so greatful you keep taking your time helping me go through all this. I wish one day i'll be able to return the favor.
While i get the general sense of it, i think i lost you a couple of time in your explanation. Therefore i think we could go on without me bothering too much about the whys and wherefores of JDBC or macros...
UnklDonald418 wrote:Those values are not easily predicted and not necessarily consecutive
You're right. Also, as for me, numbering the recipes is not that fundamental cause the name of the Pantone itself acts as identification of the recipe. Anyway, since you committed to find a solution and it could help to better distinguish at a glance the recipes we can keep it like you did.
Since CL_ID and ID_RICETTA are just numbers automatically generated to be primary keys they don't have any utility to the operators: can i get rid of them on the Report?
I noticed it shows me only the Report of the last Cartella's form i opened. Is there a way to pick a particular report?
The same question it apply to the Form CartellaDisp: the value CL_ID in the boxlist on the top is not helping, a good way to recall a aparticular Cartella could be being able to search a dossier by the value "COD." eventually restricted to a particular client (otherwise it would appear a list of hundreds of results!)
I'm so sorry i have to rush the last part of this message, but i have to get off of work asap!
Thank you, thank you, thank you.
Plomo
Re: Is OO Base good for this?
Posted: Tue Feb 12, 2019 7:08 am
by UnklDonald418
Pantone itself acts as identification of the recipe
We determined earlier that with the current design Pantone is just one of the fields in the Recipe, so it would be possible to enter more than one recipe with the same Pantone. The unique identifier is "TBL_RICETTA"."ID_RICETTA" but when using the
JDBC database, the query for the report allows "RN" to be used as a substitute.
Since CL_ID and ID_RICETTA are just numbers automatically generated to be primary keys they don't have any utility to the operators: can i get rid of them on the Report?
No, "ID_RICETTA" doesn't need to appear on the report.
In my demonstration "TBL_Cartella" is the equivalent of what you also called "TBL_DOSSIER" and "CL_ID" is the same as "DOSSIER_ID". That is the main identification number, in English it is commonly referred to as a Work Order Number, so it should definitely appear on the report. The label for that field on the report can be renamed to anything you like, but the number is the only way to identify a specific Cartella record. For testing purposes single or double digit values for "CL_ID" are fine but in a working database you might want a number with a consistent number of digits maybe 5 or 6. When you are ready to actually begin entering real data, you can reset the counter. To reset the counter to 6 digits for "TBL_Cartella" in my demonstration database, go to
Tools>SQL and execute
Code: Select all
ALTER TABLE "TBL_Cartella" ALTER COLUMN "CL_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (start with 100000);
then select
View>Refresh Tables to inform Base of the changes. From that point any new Cartella entries would be assigned a 6 digit number for "CL_ID"
Ultimately, you will want to use a split (JDBC) database, but during the design phase they can be troublesome for some users because the table design GUI doesn't allow existing tables to be edited. Table modifications can be made using SQL commands.
There are unanswered questions but they will have to wait till tomorrow.
Re: Is OO Base good for this?
Posted: Tue Feb 12, 2019 8:53 pm
by UnklDonald418
I noticed it shows me only the Report of the last Cartella's form i opened. Is there a way to pick a particular report?
Using Cut and Paste make a copy of
Query10RE
Right click on the new query and select
Edit in SQL View and look for the lines
Code: Select all
JOIN (SELECT "TBL_Cartella"."CL_ID",
"TBL_RICETTA"."ID_RICETTA",
ROWNUM( ) AS "RN"
FROM "TBL_RICETTA",
"TBL_Cartella",
"FILTER"
WHERE "TBL_RICETTA"."CartellaID" = "TBL_Cartella"."CL_ID"
AND "TBL_Cartella"."CL_ID" = "FILTER"."F1") "QRN" ON "TBL_RICETTA"."ID_RICETTA" = "QRN"."ID_RICETTA" AND "TBL_Cartella"."CL_ID" = "QRN"."CL_ID"
Remove the line containing "FILTER" and the comma ',' after "TBL_Cartella".
Replace "FILTER"."F1" with a parameter statement, something like
:Enter_Cartella_Number.
A parameter statement begins with a colon ':' immediately followed by a character string with no spaces. You can separate words with an underscore '_' character.
Also select
Edit>Run SQL command directly or click on the
SQL icon on the toolbar so that the icon is no longer highlighted.
Save and Exit the query.
Run the new query and you should see a dialog prompting you to
Enter_Cartella_Number.
Type a valid Cartella number and select OK, the query should return a list of matching records.
Now move to the Reports area and using Cut and Paste make a copy of
ReportQ10RVE.
Right click on the new report and select
Edit to open the Report Editor.
Select
View>Report Navigator to open a
Report Navigator dialog.
In the dialog select
Report.
If the
Properties dialog isn’t visible at the right of the window, select
View>Properties to toggle it on.
On the
Data tab of the
Properties dialog use the
Content listbox to select the new parameter query.
Execute the report and you should see the
Parameter Input prompt.
Type a number and select OK to generate a report.
a good way to recall a aparticular Cartella could be being able to search a dossier by the value "COD." eventually restricted to a particular client
"TBL_Cartella"."Codice" is type VARCHAR(100). Filtering on text fields can be tricky, what are you planning on storing in that field?
Re: Is OO Base good for this?
Posted: Wed Apr 10, 2019 2:27 pm
by Plomo
..and i'm back. I guess i will keep haunting you for a while
I had to take a break and focus on other stuff. I was able to read your two last comments only few days ago. With a bit of discouragement cause with this two months break i kinda lost the track of all this. I'm going to follow your steps right away and i'll be back to you asap.
Meanwhile:
"TBL_Cartella"."Codice" is type VARCHAR(100). Filtering on text fields can be tricky, what are you planning on storing in that field?
I'll give you some examples of Codes:
3197
A40120T4161
I000708TRYCSR
902.0576
ZPFN 00042
FA03460AHBNQ00
Re: Is OO Base good for this?
Posted: Wed Apr 10, 2019 4:31 pm
by Villeroy
Tools>SQL...
Code: Select all
ALTER TABLE "TBL_Cartella" ALTER COLUMN "Codice" VARCHR_IGNORECASE(20) NOT NULL
Edit>Refresh Tables
which is 6 characters more than your longest sample, case insensitive and not allowed to be missing.