Villeroy wrote:There is exactly ONE way to edit many-to-many relations in Base forms.
I guess the explanation is: before filling the sub-form i need to fill the main form fields.
UnklDonald418 wrote:First question, do you actually need to reuse the recipes?
UnklDonald418 wrote:is there more user friendly way to differentiate the recipes than the value of "ID_RECIPE"?
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
how much of "that ink" has been used or what are the most inks used
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")
);
UnklDonald418 wrote:but if TBL_RECIPES is normalized
UnklDonald418 wrote:Unless there are always 6 of each of those values in each recipe
UnklDonald418 wrote:One trade-off is that TESTING FORM will need significant redesign.
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.
After correctly connecting Inks and Additives do you think there's more to do to normalize TBL_RECIPES?
amount of a single additive will be 0,1% up to 20%
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".A Dossier will have at least 1 and maximum 6 Pantone recipes
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
what if i create 6 forms, one with only 1 recipe-details displayed, one with only 2, one with only 3 etc etc...
can print a blank Dossier for the operators
a filled Dossier for the archive
UnklDonald418 wrote:they should be removed from TBL_RECIPES.
UnklDonald418 wrote:the one to one relationship I assumed between "Inks" and "Additives" doesn't exist.
UnklDonald418 wrote:would the efficiency gains be worth the added complexity?
UnklDonald418 wrote:With the normalized tables it would be difficult to display everything on a single Base form.
UnklDonald418 wrote:Another option would be to use the database tables and queries as a data source for a spreadsheet and print the spreadsheet.
UnklDonald418 wrote:If the operators are going to be entering data onto a blank form and not directly into the database
UnklDonald418 wrote:so saving a copy may not be necessary
SELECT * FROM "somewhere" WHERE "ID" = :Please_Enter_Item_Key
My inexperienced eyes can't see any efficiency worth the complexity. Maybe it would be just more "theoretically" correct?
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.
JOIN "TBL_Additivi" ON "TBL_Additivi"."ADDITIVI_ID" = "TBL_RicetteDet"."ADDITIVI_ID",
JOIN "TBL_Additivi" ON "TBL_Additivi"."ADDITIVI_ID" = COALESCE ( "TBL_RicetteDet"."ADDITIVI_ID", 0 )
CASEWHEN( "TBL_RicetteDet"."ADDITIVI_ID" >= 0,"TBL_Additivi"."Additivo", '--' ) AS "Additivo"
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;
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"
UnklDonald418 wrote:Those values are not easily predicted and not necessarily consecutive
Pantone itself acts as identification of the recipe
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?
ALTER TABLE "TBL_Cartella" ALTER COLUMN "CL_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (start with 100000);
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?
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"
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?
ALTER TABLE "TBL_Cartella" ALTER COLUMN "Codice" VARCHR_IGNORECASE(20) NOT NULL
Users browsing this forum: No registered users and 1 guest