Problem calculating a Subtotal

[susan siegel]

I'm having a problem with the arithmetic for a "subtotal" field that totals up a series of repeated fields.

In designing an invoice form, I have repeated fields for product id (linked to a product table with a matching product id field), price, quantity, discount and extended price.

I've got the formula working to calculate the individual extended prices, but I can't get the "subtotal" field to work using the "summary" type and then, total on the "extended price" field.

the calculation is way off base and I can't even figure out how it is generated.

I read something about "context" but I can't seem to figure this one out.

Needless to say I'm a beginner.

Thanks for any help.

Susan Siegel

[email protected]

[IdealData]

If you post the file someone might be able to help.

[Lee Smith]

Why are you using Repeating Fields?

Repeating fields have their place, but 99% of the time, you are better off using a Related file.

Lee

[susan siegel]

I used repeating fields because I had trouble setting up a portal link.

Now I'm having problems generating a Report and am wondering if the problem goes back to using the repeating fields. If so, then I have to go back to figure out the portal and start over again.

I'll try to attach a file. There are 3 tables, each in a separate file. So by sending just one file, I'm not certain if you'll see all you need to see.

Susan

Susan

Sales-New.zip

[bruceR]

That's true; we don't see what we need to see. There are LOTS of examples of basic invoice templates so it is unclear why you would have a problem with this. I also not that ProductID is a lookup field but that doesn't make any sense. Usually you enter ProductID and OTHER values (description, unit price) are looked up based on ProductID.

[susan siegel]

thanks for the insight into the use of the "lookup" field. I was wondering if lookup should be checked for the fields that were related (the right word ??) to other tables. Now, I'll just use lookup for the OTHER fields.

I'm still trying to create a portal but can't get to the screen where I add fields. I did come across it before -- but don't remember how or what I clicked.

I only get as far as the portal set up dialog box. Maybe it's because I misued the lookup; I'll take a look at that.

Thanks.

susan

[email protected]

[Fenton]

ProductID cannot be a "lookup", as it must be entered manually (drop-down list of ProductIDs, show also Product Name usually; in 8 you can hide the ID, if desired). Other data pertaining to the product could either be lookups, or just related data (if you always want the latest info, such as description). Price data, on the other hand, would always be a lookup, as you don't want it to update later for any reason.

To switch away from repeats, which you must do if you want product sales reports, just create another table within the Sales file. Recreate the "repeat" fields, but not as repeats (ie., 1).*

The relationship from Invoice (your main table; name your tables properly) to InvItems (or whatever -) would be on Invoice#; which you've properly made an auto-enter ID. It would NOT be auto-enter in InvItems. It would be auto-created by the above relationship, by checking the [x] Allow creation of related records, in the relationship dialog.

You might want to do something with Tax, as it's just manually entered right now.

*The layout looks almost exactly the same, you can just add the portal and reassign your fields. Likewise you can just import your current data to the new structure, choosing the option "Split data in repeating fields into separate records"

Sales-fej.zip

[susan siegel]

Thanks for the insights.

After reviewing earlier posts and reading and rereading, I've solve maybe about 75% of the problem.

I think the key to my problem was that I didn't have a "line item" file. I now have 4 related files with a portal in the products file(aka bhp inventory) for the products.

1. customer

2. products

3. line item (where new sales are entered)

4. invoice

I can now add product description, price, etc. to the line item file and from there to the invoice file.

My remaining problem is how to generate a report from the line item file showing # of "x" product sold and $ of "x" product sold over a given time frame. Standard type of stuff.

My problem my be that I don't have my product id in a drop down box as you suggested; when entering a new product, I simply hit enter and so the next product is on a new line.

Or - I may have to set up a second portal -- I'm not sure where.

Here are my product and line item files as I think this is where the problem may be (and I can only add 2).

I added qty, disc and extended price to my inventory file. It works -- although I'm not sure that's the best and neatest place to put that info.

Thanks again.

susan

line_item.zip

BHP_Inventory.zip