common editable values linked from a field?

[nx7t]

I am a newby and it seems like it should be a simple thing but I am afraid I don't know exactly how to ask the question.

I am working on a database of products, and there are far to many products to keep track of individually (over 11,000). I decided what might work is to have a set of three multipliers one of which would be specific each product and two more one for a category, the other for the manufacturer. Multiplying these three times the value of the cost would allow adjustment of hundreds of products with different prices all at the same time.

What I have is a field for each of these multipliers, I have assigned an arbitrary number to start with for the individual product variable and of course the cost.

The category and manufacturer have two fields each, one with a value for the category or manufacturer involved and one for the value that each should use as a multiplier.

I want to be able to change the multiplier for one product and have all the products sharing that category or manufacturer to change with it.

How do I do it?

Thanks

Mike

[Tom Cone Jr]

Re: common editable values linked from a field?

Show us the relevant portions of your table structure, and then walk us through the steps you'd take if you were to do it manually. Step by step. We don't know your app like you do.

And, my math is a bit rusty, but I don't think you'd multiply the 3 multipliers (as you said), instead I think you'd sum them, and then multiply the sum against the cost. Right?

A peek at a sample data set would help us help you, too.

[nx7t]

Re: common editable values linked from a field?

The sample data is attached, it is in a tab delimited format txt file.

Yes, I did mean multiply. The idea being that the manufacturer and category multipliers would start out at one "1" which of course does not change the result. Increasing and decreasing from that would increase similar to a percentage of the price. If the results were added prices that were only at a dollar or two would NOT be proportionately changed with items that may be $25, $50 or more.

The Mult_BASE of 1.60 sets up a starting margin or 60% or 1.6 times the cost of the item.

This part I have working already the calculated field expression is: MULT_BASE*CAT_MULT*MAN_MULT

The Man_ID and the Cat_id represent the manufacturer and category ID respectively. Any change in the Man_mult column table for any product having the same Man_id needs to change as well. Likewise for any item with the same Cat_id the Cat_mult should change in all products having the same Cat_id.

So as I go through the price list, once the relationships are set up between the products a limited number of changes will need to be made to the database as a whole in order to produce changes in dozens, some times hundreds of products. If any products are way out of line I can change the Mult_BASE for that product individually.

I think I will also need to have another multiplier that moves the entire price list up and down regardless of manufacturer or category but that is an easy task once the rest of it is done.

I took alphafive at their word when they said no database experience was needed. I've had the software for some time but this is as far as I've ever gotten towards a practical application.

I appreciate the help. I may be making this more difficult than need be, but dealing with so many products I don't see any other way to do it... I am open to suggestions. There are currently over 11,000 items and I anticipate ending up with nearly 25,000 when all is said and done.

Thanks again,

Mike

[Tom Cone Jr]

Re: common editable values linked from a field?

Bulk changes to a field in related records is often done using an "update" operation. Take a look at the Batch Operations chapter of the User's Guide for an overview and examples. The update operation is akin to a automatic search and replace in your word processor. This means you can do a lot of damage quickly, so make a couple of good backups of your data before experimenting.

Instead of automatically changing all the related multiplier field values when any change occurs for a product, I'd recommend you do these kinds of bulk changes by hand, from the control panel, until you have more experience with Alpha Five. Once you've mastered designing and running an update operation by hand, we can talk about automating it.

-- tom