Apache OpenOffice Community Forum

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.

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!

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

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.

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.

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.

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:

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.

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.

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!

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   Expand viewCollapse view

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.

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 .

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.

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)

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   Expand viewCollapse view

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.

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.

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

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   Expand viewCollapse view

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   Expand viewCollapse view

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   Expand viewCollapse view

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.

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   Expand viewCollapse view

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.

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   Expand viewCollapse view

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/6dz6o2bi687vnnp/DB_CartelleDemoSplit.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.

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

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   Expand viewCollapse view

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.

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   Expand viewCollapse view

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 [b] Enter_Cartella_Number[/b].
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?

..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

Tools>SQL...

Code: Select all   Expand viewCollapse view

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.