Stored calculations

[Johnny5]

Hi when do stored calculations recalculate? Can you force it with a script step? How about global calculations. How do they work and recalcuate?

[Ender]

They are stored when the fields they depend on are modified. There's no need to recalculate them unless they're referencing globals, related fields, or get() functions, but in those cases, the calc should be unstored.

[Johnny5]

Yes I understand what you mean Ender but I can not have the field unstored because I cant use it as a key field then. My relationship goes from a global field in the parent interface file to a calculation field in the child records in the data file. So I figured since I cant have it unstored, each time the user updates the related child table I can run a script or something to update the calculation field.

The setup is this:

Vendors --> VendorCategories (join table)

When the user adds a new record in VendorCategories, I would like the calculation field in Vendors to update. The calculation field will just be a list of all associated vendor categories for that particular vendor. Again it can not be unstored because from the interface file I need to be able to select a global field with the category and be able to return all vendors that haev been assigned that category. so I was figuring if when they run the script to add the new record to VendorCategories, I can have the calculation update.

[Ender]

Well, you could update a regular text or number field via script, but there's usually a way to rework the relationship so that the child match key is stored and the parent key is what's calculated.

If you can provide more details about what you're trying to do and specify what your current match fields are, we may be able to help.

[Johnny5]

Here is the basic setup.

2 files. 1 for interface that has globals and most relationships in it.

The other is the data file which have the tables, Vendor, Vendorcategories (join), Categories.

Categories is basically a list of all available categories. Vendor is a list of vendors, and Vendorcategories (join), is just a many to many join table which contains VendorID and CategoryID.

So in the Vendor table, I have a calculation field which returns a valuelist of the related records from Vendorcategories (join) called cAssigned.

So the problem is that when I am on my interface file. I want to have a relationship so that when I set a global field to a vendor Category ID, and I want to see all the vendors that have that Category associated with them in the join table to display in a portal.

I also want to be able to further filter the portal by lets say another field in the Vendor table called cFirstDigitName which is basically just the first letter of the vendor name.

A second global field from my interface file gLetter should relate to cFirstDigitName. So the multi key relationship is gVendorCategoryID = cAssigned and gLetter = cFirstDigitName.

Herein lie the problem. If a new record is added into the Vendorcategories (join) table, the cAssigned field is updated because it is an unstored calc. But since it is unstored the portal doesnt work. I figured that if I made cAssigned stored and when the user enters a new record in the join table it could somehow update cAssigned it would work.

I guess I can have a text field in the Vendors table that via script is just set to the list of related items in the join table but I am not sure if this would be the right approach.

[Ender]

Why not link directly to the Vendor_Category join table? You'll need a Vendor Name defined with a lookup in order to filter by the name, but I think it should work. If you wish to see other Vendor fields, just place them in the Vendor_Category portal.

[Johnny5]

Hi Ender,

I was thinking about basing it off the Vendor_Category join table intially but there is acutally one more field that has a third key match. That one is global to stored calc in the Vendor table.

Also I wanted the database to have as less redundant data as possible. I think the only viable solution is to set a text field in the Vendor table to the calc field value via a script when a new record in the join table is created.