Auto Entry field and a calculation HELP

[tryphe]

Okay I am so frustrated right now! I have made several databases over the last few years and just last week my boss informs me I have been doing them all wrong. Now I am self taught and my databases have all been working but I have never used the Auto entry function. My boss says I should be using it for all prices that way if and when we update them we won't affect all the previous invoices and calculations. We are talking about alot of prices. so do I really need a separate field for each price and in the auto entry data field put the price? Is that the right way to do it?

I tried it with the new billing database I am building but now my calculations aren't working. They did before I switched to using the field with the auto entry data. What am I doing wrong?

The pricing calculation is used as part of another calculation that reads

If(ad="Yes", price calculation, 0"

It all worked before the way I originally did it but not since I redid it the waymy boss said it should be done.

Here is what the pricing calculation looked like before

Case(Ad Letter="A" and Colour="black", 75, 0" (this is just a small sample of a very long calculation but it is all basically the same idea with "B" , 75 and such)

the New Calculation is Case(Ad Letter="A" and Colour="black", New 75 auto field, 0"

New 75 auto field is Number field with data check marked in the auto entry and the number 75 supplied for data. But it doesn't work. Nothing happens. AAAAAAUUGHH what am I doing wrong my boss says this is bsic filemaker and that I should know this but hey I don't.

Any suggestions or help would be greatly appreciated. WHat is the right way to do a billing database on that won't affect all the past billing when prices are updated. Is he right and the way I did it was just stupid? If so then how do I do it right? And get all my calculations to work? I am so frustated right now. So much work and time wasted. Am I making any sense at all?

HELP Please HELP

[RalphL]

Let me guess at you configuration. You have a products file which has a Product ID, Product Name, Product Price. In your invoice file you have a portal to invoice line items. The line items contain the Product ID, and use a relationship to get the Product Name & Product Price.

Instead of a relation use a lookup to get the Product Price. This is the auto entry that you should be using. If the price is changed it will not change the value of previouly looked prices.

[tryphe]

Umm nope don't have a product file. We are a magazine publishing company so it isn't like that I am doing a billing based on ads supplied for a particular issue. One of the Prices is based on how many colours it is, if is new, what the size is. Another price is based on whether there is film needed or production needed. All of this info is taken from the same database that my designer fills out as she gets the ads in. The billing is just another layout in the same database. so no Product name no product price no relationships at all ...the calculations are just as I laid out in my original post. They just don't work when I use the auto look up. Should I be putting the prices in a separate database?

[Ender]

So when prices change, you have to edit the calculation? Yikes!

Although you could change your current Price field to a number field with an Auto-Entered Calculation, this design is not ideal. You should definitely look at using a related file for Products.

[tryphe]

I know bad me I am fixing it and making a separate database with pricing and relating it back as you suggested. Thank you.

I am just having one more problem the first set of calculations and look ups work great but the next pricing isn't. I made a relationship based for the first set of pricing for Colour keys it is related by Colour Key Code which is calculated based on several factors. THe look up looks up the price great. But when I did the exact same thing for the next set of prices Film and related it back to the pricing database it is taking the colour key prices instead of the film prices. The Film codes are again determined on a number of factors to get the code. But shouldn't it be only giving me the price that is listed in the pricing database for that code. IT is two different relationships so I don't even know how it is getting the Colour Key code price.

My god I am just reading this over and I realize I am making no sense. I am just not explaining this well.

See below for what I am doing....maybe that will help explain what I can not.

Film Service code: Case(Ad Letter="A" and Colour="Black","F1CA", Ad Letter="A" and Colour="2C", "F2CA", Ad Letter="A" and Colour="4C", "F4CA", Ad Letter="B" and Colour="Black","F1CB", Ad Letter="B" and Colour="2C", "F2CB", Ad Letter="B" and Colour="4C", "F4CB", Ad Letter="C" and Colour="Black","F1CB", Ad Letter="C" and Colour="2C", "F2CB", "") (one part of a long calculation) So the service code is determined.

The next calculation...

Film Price: Case(Film service codes="F1CA", F1CA, Film service codes="F2CA", F2CA, Film service codes="F4CA", F4CA, Film service codes="F1CB", F1CB, Film service codes="F2CB", F2CB, "")

Now Fields F1CA and F2CA and so forth are number lookup fields values imput by a relationship.

The relationship is Film pricing relationship ---Film service code:price code

then copy price field.

everything works except it is giving me the Colour Key prices ignoring the Film service codes.

THe Colour Key calculations are similar and work perfectly

CK code: Case(Ad Letter="A", "CK1", Ad Letter="B", "CK2", Ad Letter="C", "CK2", Ad Letter="D", "CK2", Ad Letter="E", "CK3", Ad Letter="F", "")

then

Colour KEy amount: Case(CK Service Codes="CK1", Colour Key 75, CK Service Codes="CK2", Colour Key 37.50, CK Service Codes="CK2", Colour Key 37.50, 0)

The colour Key fields are lookup number fields valued by a defined relationship

Colour Key relationship---CK codes:Pricing then copy pricing field.

It should work shouldn't it? But it doesn't the colour key works perfect but then the film always gives me the Colour key price it is the lookup relationship that is screwed but I can't figure out why.

Please Help!!

[Ender]

What I do when I get wierd lookup results is drop portals on a layout with the lookup relationship in question, showing ID fields and lookups fields. This way you can see what the lookup relationship is looking at. There should only be one portal row if it's the right relationship.

[tryphe]

I LOVE YOU no really I do

I tried the portal thing and realized I had set my service code to numbers instead of text which is why the calculation wasn't working. It is all working perfectly now.

Doing the happy dance singing the happy song La la la la