prevent changes from affecting previous records

[Chemadian]

First off, this is the sequence/relationship of my tables.

Ingredients --->FormIngred (a join table) --->Compound Formula ---> Audit Form

The Compound Formula table contains a bunch of formula's, these formula's can essentially be "copied" (i use this term very loosely) and an "audit" is created. The audit serves almost like an invoice in that it records each time a certain formula is made.

The problem is that if a formula is changed in the compound formula table, these modifications affect all the audit's that are connected to that particular formula. This cannot happen, audit's that already exist cannot be modified even if its related formula happens to change. Is there a way to prevent these changes from affecting existing audit records?

Cheers

[comment]

You probably want to use a lookup. Hard to say anything more definite with a "very loosely termed" description.

[Chemadian]

sorry for my ambiguous post, i was in a rush last night and obviously didn't do a good job explaining my situation.

It's true that most of my fields on the audit form are in fact look-ups from the compound formula. The fields that i'm having trouble with are in a portal on the audit layout. The two fields in the portal are; "ingredients" and "amounts", which are not look-ups they are actually related fields from the FormIngred table.

The two portal fields are related fields and not look-ups because i have a script that sets values into these fields from a portal located on the compound formula and i can't get the script to work with look-up fields. When i try and use look-up fields only the value in the first portal row gets set into the audit form portal, given that it does set as many rows as it should, it just doesn't move on to set the next consecutive portal row. I can see this being confusing so i will give an example:

Compound Formula Portal

Row 1--> Ingredient #1, Amount #1

Row 2--> Ingredient #2, Amount #2

Row 3--> Ingredient #3, Amount #3

Row 4--> Ingredient #4, Amount #4

Audit Form Portal

Row 1--> Ingredient #1, Amount #1

Row 2--> Ingredient #1, Amount #1

Row 3--> Ingredient #1, Amount #1

Row 4--> Ingredient #1, Amount #1

So i guess my real need is to figure out if i can get my script to use look-up fields.

My script is as follows:

If [ FormIngred ::Ingredients ]

Go to Related Record [ From table: “FormIngred”; Using layout: “FormIngred” (FormIngred) ]

[ Show only related records ]

Go to Record/Request/Page

[ First ]

Loop

Set Variable [ $IngredName; Value:$IngredName & Left ( "¶" ; Length ( $IngredName ) ) & FormIngred::

Ingredients ]

Set Variable [ $Amount; Value:$amount & Left ( "¶" ; Length ( $Amount ) ) & FormIngred::Amount ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

End If

Set Variable [ $AuditID; Value:Audit Form::_FK_Compund ]

Set Variable [ $i; Value:1 ]

Go to Portal Row

[ Select; First ]

Loop

Exit Loop If [ $i > ValueCount ( $IngredName ) ]

Set Field [ Audit Form::Ingredient; GetValue ( $IngredName ; $i ) ]

Set Field [ Audit Form::Amount; GetValue ( $Amount ; $i ) ]

Set Variable [ $i; Value:$i +1 ]

Go to Portal Row

[ Select; Next; Exit after last ]

End Loop

Is it possible to to use this script with a look-up field instead of the related field from the FormIngred Table?

[comment]

You mention "a portal on the audit layout" - it's not clear to which table and for what purpose.

IMHO, you should create a new record in the Formulas table (or in a FormulaVersions subtable) whenever you change a formula. This way, nothing needs to be copied and each data element is recorded in one place only.

Another option is to use two fields in the Audits table and have them auto-enter calculations that list the related ingredients and their amounts, respectively (I think that's what your script is trying to achieve anyway). But it seems like a rather clunky approach.

---

BTW, it looks like your join table holds the ingredient name - it should keep only the IngredientID.

[Chemadian]

You mention "a portal on the audit layout" - it's not clear to which table and for what purpose.

The two fields in the audit portal are; "ingredients" and "amounts", they are related fields from the FormIngred table. The portal is essentially used for the same purpose as the portal in the compound formula, it displays multiple ingredients. I hope this is enough explanation, if not i need some additional clarification on what information you are looking for.

your honest opinion is correct, a new formula record is created for each formula ( we have lots of very similar formula's most just change in quantity or strength, which then change the amount of ingredient used). There is the occasional exception when feel it necessary change an existing formula without creating a new one. This is limited to instances when further experience/testing helps us refine our existing formula.

I like the idea of an auto-enter calculation that will list the Audit forms ingredients and amounts from it's associated formula, but would that prevent the audit form ingredient and amount fields from being changed if its associated formula is modified? I'm open to that idea if it's possible, i just can't wrap my head around how it would work.

---

BTW, it looks like your join table holds the ingredient name - it should keep only the IngredientID.

I admit i have issues with join tables, and you are probably correct but maybe i can get some clarification if i try and explain my setup.

The Ingredient table has one field "ingredientName". The compound formula has two fields that i think associate with the ingredient table "Ingredients" and "amounts"

The relationship between the ingredient table and the compound formula is a many-to-many relationship, justifying the join table. Would you not want both the amount field and ingredient field in the join table because both of the fields don't truly define a formula record.

[comment]

I like the idea of an auto-enter calculation that will list the Audit forms ingredients and amounts from it's associated formula, but would that prevent the audit form ingredient and amount fields from being changed if its associated formula is modified?

It's essentially the same thing as lookup - except it "looks up" the result of a calculation using the List() function. However, as I said earlier, it seems like an unnecessary duplication of data. Let's say you have a formula A with 3 ingredients. After having produced 50 batches of A, you change the quantities slightly and produce another 50 batches of A'. You now have 50 records replicating the original A formula and another 50 records with the current A' formula - all this instead of having two records representing the two formulas, with 50 related batches each.

The Ingredient table has one field "ingredientName".

It should have two fields: IngredientID (auto-entered serial number) and IngredientName.

The two fields in the audit portal are; "ingredients" and "amounts", they are related fields from the FormIngred table.

Using your approach, such portal can be misleading. Take the above example: the portal in all batches shows the "ingredients" and "amounts" for the current version A' - but the first 50 were made using the original A formula.

[Chemadian]

After considering the points you brought up in your last post, it does seem like there is a lot of duplication going on. Can you explain to me what these "batch" records are and how they differ from my current audit records?

Maybe if i explain the reason for building this database it would help decide if "batch" records are appropriate.

I work in a university chem lab and currently we have a bunch of chemical formula's written out and stored in binders. This is the first reason for building the database, to essentially copy all of the formula's from the binders into the database for easier reference. These records are stored in the compound formula table.

Every time we make a new batch of one of these formula's, we reference the the formula record stored in the compound formula table (sort of like a master copy) and then we make an "audit form" of that formula.

The audit form is somewhat a copy of the compound formula (has the same ingredients and amounts) but it also has more specific information that changes with each batch, example Date prepared. On this audit we also have to store the specific information regarding each ingredient used, this info is found in the inventory table (at this time not being implemented because i am still working out some kinks). Anyways, there are three fields from the inventory table that eventually will need to be stored on the audit form "lot#" "source" "Expiry", currently when we print the audit form these fields are blank and we just write them in by hand.

Overall these audit forms are very important, and must be stored independently from the formula. Any ideas/suggestions on how i can make this work with as little duplication of data as possible is appreciated!

[comment]

what these "batch" records are and how they differ from my current audit records?

I believe they are the same - it's a record created for each instance of implementing a formula to produce a compound. I just think Batches is the appropriate name for a table where each record is a batch. "Audit Form" may be a suitable name for a layout of such table.

we also have to store the specific information regarding each ingredient used, ... "lot#" "source" "Expiry"

Do you mean that every time you produce a compound, you need to record something specific about each ingredient used for that batch? This would change everything. You would not only have to duplicate the formula, you would also need a second join table to link Batches to Ingredients and/or to Inventory.

[Chemadian]

Whew, i'm glad to hear that "batch" and audit records are the same thing, means i'm not completely out in left field...maybe just left-center!

Do you mean that every time you produce a compound, you need to record something specific about each ingredient used for that batch? This would change everything. You would not only have to duplicate the formula, you would also need a second join table to link Batches to Ingredients and/or to Inventory.

Thats exactly right, and i do have the structure in place to implement this. I have an inventory table connected to a AuditInv (join table), which brings up another question.

Currently i have an Ingredients table which has only two fields, primary key and the ingredientname field. This table is linked to FormIngred (join table) and then to the compound formula table. I created this Ingredient table to contain each ingredient record once because i knew that the inventory table would contain multiple records of each ingredient (create a new record each time we receive an ingredient in our order). So i thought it would be a good idea to use an ingredient table (approx 500 records) to search through and populate the compound records, compared to the inventory table (approx 5000 records). Also the compound formula doesn't need access to all of the other ingredient information that is found in the inventory table, just the Ingredient Name

To ensure that the IngredientName fields in both the inventory and ingredient tables match, the inventory::Ingredientname is populated by a value list generated by the ingredient::Ingredientname. So when a user creates a new record the Inventory::ingredientName field auto-completes using the value list. I do have a script trigger set in the Inventory::IngredientName that will automatically create a new record in the ingredient table with the new ingredient...hope all of this makes sense because just writing it out makes it seem clunky/messy.

Basically what i want to know, is it proper technique to have a setup like this or should i scrap the ingredient table and just use inventory table for both instances?

Cheers

[comment]

The name of an ingredient should be in the Ingredients table only. The other tables - FormIngred and Inventory - should store only the selected IngredientID. Once you have selected an ingredient (by entering its ID into a foreign key field), its name becomes available through the relationship.

There are some situations where you can use a name instead of a meaningless ID. It's when the names are both permanent and unique (for example, you don't need IDs for a table of US states or elements of the periodic table). Furthermore, if you have nothing to say about the object other than its name, you can replace the table with a custom value list (provided you don't add/delete values as a routine operation).

[Chemadian]

The name of an ingredient should be in the Ingredients table only. The other tables - FormIngred and Inventory - should store only the selected IngredientID. Once you have selected an ingredient (by entering its ID into a foreign key field), its name becomes available through the relationship.

It makes sense that the Ingredient Name should only be found in one table, currently i have the Inventory and Ingredient tables linked by the IngredientName field in each table...i guess not the best way to go even though the names are relatively permanent and unique.

For the time being i think i will keep this relationship until i run into problems because it seems to save the user from having to input a meaningless ID, instead they can just enter the Ingredientname and be done.

Because we do add new ingredients on a regular basis, it seems necessary to keep the value list linked to the ingredient table instead of creating a custom value list.

-------

Back to the original problem, you mentioned using an auto-enter calculation that will essentially act as a look-up instead of using a related field and that long script from an earlier post. So i guess what i need is the functionality from that script but the properties of a look-up field instead of a related field.

I'm thinking something like along the lines of the script above but as you suggested adding the list ( ) function may eliminate some of those steps, maybe the loops? But i'm not exactly sure how the list ( ) function works in a portal.

Thanks for all your help

Cheers

[comment]

to save the user from having to input a meaningless ID,

That's only a matter of setting the value list to show the "second" field.

you mentioned using an auto-enter calculation that will essentially act as a look-up

That's not going to work, because you need to duplicate each related record in the FormIngred table as an individual record in the other join table (AuditInv).

The script for this would look something like:

Set Variable [ $compoundID ; Compounds::CompoundID ]

Set Variable [ $ingredientIDs ; List ( FormIngred::IngredientID ) ]

Set Variable [ $quantities ; List ( FormIngred::Quantity ) ]

#

Go to Layout [ Batches ]

New Record

Set Field [ Batches::CompoundID ; $compoundID ]

Commit Records

Set Variable [ $batchID ; Batches::BatchID ]

#

Go to Layout [ AuditInv ]

Loop

Set Variable [ $i ; $i +1 ]

Exit Loop If [ $i > ValueCount ( $ingredientIDs ) ]

New Record

Set Field [ AuditInv::BatchID ; $batchID ]

Set Field [ AuditInv::IngredientID ; GetValue ( $ingredientIDs ; $i ) ]

Set Field [ AuditInv::Quantity; GetValue ( $quantities ; $i ) ]

End Loop

#

Go to Layout [ Batches ]

There is an assumption here that [a] the script starts at the Compound record; and the IngredientID and Quantity fields in the FormIngred table cannot be empty.

[Chemadian]

Comment,

The script seems close to working except that when the IngredientName and Amount are displayed in the Audit Form portal the number of ingredients are displayed in each row. Meaning that if i have 4 ingredients used in a particular formula, all 4 ingredients are contained in each portal row instead of the first ingredient in the first portal row, second ingredient in the second portal row etc...

I tried to modify i couple script steps from the original script but i still couldn't get it to work. I think its because i'm a little confused with these steps:

Set Field [ AuditInv::BatchID ; $batchID ]

Set Field [ AuditInv::IngredientID ; GetValue ( $ingredientIDs ; $i ) ]

Set Field [ AuditInv::Quantity; GetValue ( $quantities ; $i ) ]

Wouldn't you want those set fields to target the Audit Form instead of the join table between the audit form and inventory?

[comment]

all 4 ingredients are contained in each portal row

I don't think that's what should be happening.

Wouldn't you want those set fields to target the Audit Form instead of the join table between the audit form and inventory?

No. Let me remind you of my question:

Do you mean that every time you produce a compound, you need to record something specific about each ingredient used for that batch?

It is not possible to record "something specific about each ingredient used in a batch" unless you have a record for each.

[Chemadian]

I don't think that's what should be happening.

Ya i agree that shouldn't be happening. I have my IngredientName and Amount fields in a portal on the audit form, from which join table are they supposed to look up their information from? When i set the fields to look up from the FormIngred join table there are multiple ingredients in each row, when i set the fields to look up from AuditInv the rows are empty.

It is not possible to record "something specific about each ingredient used in a batch" unless you have a record for each.

Ok, that makes sense i just don't see steps that will set these values into its own distinct portal row on the audit form.

[comment]

I have my IngredientName and Amount fields in a portal on the audit form, from which join table are they supposed to look up their information from?

You can have two portals on the audit form - one to the FormIngred table, to show the required quantities, and another to the AuditInv table, to show the actual quantities used to make this batch.

The script creates the necessary records in the AuditInv table, and presets them to the required values, so that initially the two portals will look the same.

[Chemadian]

Oh ok that makes sense, thanks for the explanation.

I'm still having trouble with the portal rows on the audit form displaying the first ingredient instead of each portal row displaying it's own ingredient. Currently i have the portal with two fields AuditForm::Ingredients and AuditForm::Amount and they look-up their values from the FormIngred table. The portal is then defined to display related records from the FormIngred table.

I now know that the Ingredient and amount fields need to be look-up fields in order prevent change if the related field is modified, but don't fields in portals need to be related fields instead of look-up fields? Could this be where my problem is coming from?

[comment]

Currently i have the portal with two fields AuditForm::Ingredients and AuditForm::Amount and they look-up their values from the FormIngred table.

I am afraid that doesn't make sense. The audit form table shouldn't even have those fields.

[Chemadian]

I'm confused, what fields should be there that would permit look-up functionality? Previously i had related fields in the portal and everything worked fine EXCEPT that when an ingredient or amount changes in the formula it affects all the existing audit forms related to that formula...How do i fix this?

[comment]

I think we need to back up a bit to understand where we are now:

At first, we were talking about copying the entire formula into two fields of AuditForm table. Thus a formula with 4 ingredients would be stored as 4 return-separated values in AuditForm::Ingredients and 4 return-separated values in AuditForm::Amounts.

Such method would be suitable for taking a "snapshot" of the formula - with no further adjustments, and no information added to the individual items during the production process.

Then you made clear that the last condition is not true. So scratch the entire idea, and move to copying the formula by replicating the 4 records in the FormIngred table as 4 individual records in the AuditInv table. Since these records are created and populated by script, no actual lookup is necessary.

[Chemadian]

Then you made clear that the last condition is not true. So scratch the entire idea, and move to copying the formula by replicating the 4 records in the FormIngred table as 4 individual records in the AuditInv table. Since these records are created and populated by script, no actual lookup is necessary.

This makes sense to me, what i'm having trouble with is populating the AuditForm because when i try and populate the portal with records from the AuditInv table no ingredients show up. When i look at the AuditInv layout the fields _FK_Inventory and Amount both show question marks, which seems to be the likely problem. I checked the script steps which involve setting fields in the AuditInv table and the Get(value) calculation seems to be correct so i'm a little lost with what is going wrong.

Thanks for your extended help!

[comment]

Well, it seems like an implementation problem.

[Chemadian]

Ya i obviously have some more problems probably took on a little too ambitious of a project for a first timer. Live and learn i guess

I started snooping around and i found that the FormIngred::_FK_IngredID field does not contain a value when a new formula is created. I checked the relationship between the Ingredient and FormIngred tables and it allows the creation of records in the FormIngred table so any idea on what would cause this?

I also started going through your script to try and understand all the steps and i have some questions/problems. First,

Set Variable [ $ingredientIDs ; List ( FormIngred::IngredientID ) ]

This step is obviously a problem because of my first question, but further i think i am missing some fundamental understanding with this.

So this is my relationship between the ingredient and FormIngred tables: Ingredient::_PK_IngredID ---> FormIngred::_FK_IngredID. The join table also has "ingredient" and "Amount" fields. The portal on the compound formula layout displays these two related fields and the "ingredient" field is populated by value list based on the ingredient table (Ingredient::IngredientName). From what i've learned it seems like the "ingredient" and "amount" fields shouldn't be in the join table at all. So if i understand correctly the portal should display the related field "IngredientName" from the ingredient table and the "amount" field should most likely not be a related field at all but be based on the compound formula table?

Next Question:

Set Field [ AuditInv::IngredientID ; GetValue ( $ingredientIDs ; $i ) ]

I don't really have a question about the step itself more of a background question.

As i said in an earlier post i have two tables...which i guess could essentially be one table, they are the Ingredient and Inventory tables. The ingredient table has two fields, its primary key and ingredient name. The inventory table has its own primary key, an ingredient name field, and multiple other fields regarding specific information such order number, Cost/unit, lot number etc.

The reason i split them up is because the inventory table has multiple records of the same ingredient (as we receive orders) and i wanted to keep the ingredient table so that it would contain only one record for each ingredient we have.

The current relationship is as follows:

AuditForm::_PK_AuditID ---> AuditInv::_FK_AuditID; AuditInv::_FK_InvID ---> Inventory::_PK_InvID; Inventory::IngredientName ---> Ingredient Copy::IngredientName

I'm not sure this last relationship is good technique but i use it so if we receive a brand new ingredient in the order we can enter it in the inventory table and there is a script that will make sure it is not a duplicate and it will copy the IngredientName field from the inventory table into the IngredientName field in the Ingredient table.

Anyways i just wanted to make sure that the Set Field [ AuditInv::IngredientID ; GetValue ( $ingredientIDs ; $i ) ] script step will work with how my relationships are currently setup.

I apologize for the long post, but i just want to make sure my relationships are setup correctly so i can fix what needs to be fixed and move on with a correctly structured database. Thanks again for all of your help, its been an invaluable learning experience to say the least!

[comment]

i just want to make sure my relationships are setup correctly

Perhaps it's time to have a working file. Hopefully this will make things clearer.

Recipes&Batches.zip

As i said in an earlier post i have two tables...which i guess could essentially be one table, they are the Ingredient and Inventory tables.

No, they cannot be one table. There is a one-to-many relationship between these entities: one ingredient can have many inventory entries.

The inventory table has its own primary key, an ingredient name field, and multiple other fields regarding specific information such order number, Cost/unit, lot number etc.

The Inventory table should have an IngredientID, not ingredient name.

[Chemadian]

Comment,

Thanks for the working file, just seeing the way you set it up helps clarify things immensely!

A couple more questions; first, why does the ::Quantity field belong in the ReqQuantity table and not in the recipe table?

Second, How would i go about setting up an inventory table? I've tried setting it in between the ActualQuantity and Materials tables and also tried creating another TO for the materials table and couldn't get either of these to work

[comment]

why does the ::Quantity field belong in the ReqQuantity table and not in the recipe table?

Does a recipe have an attribute of quantity? If yes, a quantity of what?

[Chemadian]

Ok, so what i think your getting at is that because its not really the recipe itself that has an attribute of quantity but instead its the ingredients that make up the recipe...hence the amount field belongs in the join table. If thats what your getting at i now understand...if not i'm more confused!

Also, if you can show me how to integrate my inventory table into your working solution that would be greatly appreciated!

Many thanks,

Cheers!

[comment]

i think your getting at is that because its not really the recipe itself that has an attribute of quantity but instead its the ingredients that make up the recipe...

Well, actually a recipe CAN have a quantity attribute, e.g. "4 servings". But in a database one would expect this to be normalized.

How would i go about setting up an inventory table? I've tried setting it in between the ActualQuantity and Materials tables

That is where it belongs, I believe.

[Chemadian]

Comment,

Ok this is what is currently working, but i wanted to check if this setup is correct.

I've connected the inventory table to the actualquantities and the material2 tables via this relationship

ActualQuantities::InvID ---> Inventory::InvID ; Inventory::MaterialID ---> Material2::MaterialID

In order for the ActualQuantity portal to display the materialID and material fields i created another TO of the material table (material3) with this relationship

ActualQuantities::MaterialID ---> Material3::MaterialID

I know these are very elementary relationship's but i want to make sure i'm headed in the right direction

[comment]

I've connected the inventory table to the actualquantities and the material2 tables via this relationship

ActualQuantities::InvID ---> Inventory::InvID ; Inventory::MaterialID ---> Material2::MaterialID

That seems about right. Except it should be written as:

ActualQuantities::InvID = Inventory::InvID

Inventory::MaterialID = Materials 2::MaterialID

or shortly:

ActualQuantities >-- Inventory >-- Materials 2

In order for the ActualQuantity portal to display the materialID and material fields i created another TO of the material table (material3) with this relationship

ActualQuantities::MaterialID ---> Material3::MaterialID

Don't quite see the point of that.

[Chemadian]

I didn't think another TO was necessary either until i discovered that the ActualQuantity portal won't display the the materialID or the Material fields.

Is it because the portal on the batches layout won't look past the inventory table and grab the values from the material2 table?

[comment]

the portal on the batches layout won't look past the inventory table and grab the values from the material2 table?

It will - once there is a related record in Inventory. If you merely to wish to display the material's name, before you have selected an inventory record, you could use a popup menu - or the extra relationship.

[Chemadian]

Many thanks comment!