Find specific related field

[rcarbaugh]

I need to find two specific fields in LineItems from within another table. I tried Lookups, but I apparently don't know how to get a specific field, and I tried a multi-criteria relationship, but it seemed pretty cumbersome.

Here's the tedious explanation:

I have the standard Invoice, Products, Line items, Addresses setup with an additional Checks file. Checks, among many other fields, displays the LineItems sold on a particular concert night, where Invoices displays the LineItems sold on our website. In the Checks layout, I have a portal with all the items sold at that event. However, we discount sales according to volume, so 3 CDs sold separately will be $45, but together will be $40. To accommodate the difference in the $$ received (Gross) and the $$ returned by standard pricing from Products (LineItemsTotal), I enter a Discount record in LineItems (from the portal in Checks) to reconcile the LineItems total with the actual $$ received (Gross).

My goal is to use the amount of the discount to calculate the net/after discount amount that a particular item actually sold for, so that I can pay a third party for whom I am selling that product.

Now, I need to extract that Discount amount to compare it to the Gross and get a discount percentage. Then, I need to extract a specific product's sales figure (TCCD), and multiply it by that percentage, to get the amount due a third party that I am selling that product for.

The math is easy, finding the specific records is confusing.

My question is this: Let's say CheckIDNum 6266 has 10 LineItems that sold. I have a portal in Checks with all 10 listed, and make an 11th entry for the discount amount to match the amount received. Now, I want a field in Checks which gives me only the amount of that discount record (DISC from LineItems), so that I can do the math and get a percentage discount figure. Once that discount percentage is calculated in another field, I need to get another record (TCCD) from LineItems to calculate my payment to the third party.

While in Checks, how do I find a specific record in the LineItems?

Here's an example. I sell 3 TCCD, 4 JHCD and 3 GMCD for $425. LineItems shows the full, non-discounted price total of $450. So, I enter a DISC discount record of -$25 to balance. Now, the portal shows 4 lines, including the discount line item. So, I need to have FMP calculate the 25/450=5.556% discount, and come up with the 3 TCCD listed for $15 each and return $42.50 ($45 less 5.556%). Again, I can do the math, but how do I get the amounts from LineItems while I am in Checks.

The only way I know to do that is to extract the DISC amount, divide it by Gross, then multiply it by the ExtendedPrice of the TCCD LineItem.

So, how do I get only the DISC and TCCD records from LineItems in the Checks file?

Rod

PS. Actually, there are two products that I am selling for a third party, TCCD and TCTR. I am hoping that once the above problem is solved, I can use "TC*" and return all occurrences.

[rcarbaugh]

Well, here's how I did it. After not hearing back from the forum, I figured it was either really hard, or so easy that it was obvious. Dunno which it is, but I had to get it done, so here's how.

The goal was how to separated certain related fields from a group of related fields. For instance, if I have an invoice for a sale of five lineitems, A, B, Ba, C, and E, but want to also have a calculation on only the B and Ba that were sold on the invoice, how do I do it. I already have a portal with all five items, but how do I calculate with only the B and E lineitems.

The method I chose may not be the best way, but it was all I could think of. I created a text field (BTest) in my invoice table that had "B" in it for all records. Then I made a calculated text field in LineItems that returned "B" for any item which was either B or Ba. Lastly, I made a multi-criteria relationship between Invoices and LineItems that linked the InvoiceID between the tables, and also linked that BETest field with the "Bcalc" field in LineItems. A second portal on the Invoices layout lists only the B and Ba sales for each invoice, and the LineItemsTotal for that second relationship gives me the total that I can use for calculation.

Is that the best way? It sure seems to me to be goofy, especially because if the BETest text field ever got changed, the second portal would not show the correct data. But, it does work.

Would sure love some commentary from the Forum.

Rod

Edited February 2, 2008 by Guest

[David Jondreau]

I think what you need is to store the discounted amount in the Line Items record being discounted, not as an additional record. If you're selling 3 CDs for $40 when they're individually $15, that should be reflected in that item, not added on later as a discount. That would save a lot of dividing and multiplying.

In that Line Items record, you should also be storing the ID of the owner of the item. That way you can base reports on sub-summary parts sorted by the owner, and you can pull Line Items into a portal from an Owners table.

[rcarbaugh]

Thanks for looking it over, DJ. Linking LineItems with the owner is a good idea. I am basically doing that now, with a calculated LineItem field which identifies the two products that need this extra attn.

As for the discount, I have two sales sources, concert sales and web sales. My web sales data downloads to me with retail pricing on each item, then a variable discount item (5-20%) on each transaction (multiple items). Since my database imports the fields directly from the internet shopping cart, a single discount line makes my invoices look like the internet generated ones. So, I just adopted that practice with the concert sales as well.

It is kind of a "choose your battles" thing where to do the math. My frustration was with isolating a few of the LineItems from the entire invoice of LineItems. The "ownership" table is basically what I ended up with, a portal matching a text field in Invoices with the "ownership" calculated field in the LineItems record.

Thanks for looking into it with me. I guess I pretty well stumbled on it by myself, unless you see a hole in my reasoning somewhere.

Rod