Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Need help making an Invoice Calculation


rsb87

Recommended Posts

I need help with an invoice solution that I'm working on. I have a products, line items, invoice, and customer db. In the products table, the products have lets say a dealers price and then retail price. In the customers table, they are given a pricing structure which is a text field that is either dealer or retail pricing. This field is then carried over to the invoice db where I need to get the line items to look at the invoice and then decide which price from the products db needs to be used. I have all the proper relationships to each of the databases, but I can't seem to get it to work properly. I have tried using a case function but it doesn't seems to be working properly (not sure if the syntax is incorrect or what?).

 

Lastly, I need to somehow get this calculation into a lookup field since it is for price. That way if the price ever changes, it doesn't affect past invoices. Ok sorry for the long explanation. So, if anybody could help me I would greatly appreciate it, even if I have to try and complete the task in another way.

 

Thanks again.

 

RSB87

Link to comment
Share on other sites

Hi,

I take it that the two different prices are in two separate fields in the products db (rather than on separate records in a related pricing db)?

 

If that's the case, then you won't be able to use a straight and simple lookup to bring the correct price into your line items records, since a lookup by its nature, targets a specific field. You would have to either bring in both the prices (by lookups into separate fields in the line items record), then reference the appropriate one by calculation, or set an unstored calculation in lineitems to discern the correct price, and then set a lookup (based on a self-join) to capture the price to a data field. This latter approach is more complex to set up, but will have several advantages. It will be more efficient, plus if the customer's price indicator changes, historical data will not be affected.

 

Either of these approaches will require that you are able to reference the price indicator (from the customers table) from the lineitems table. One way of doing this would be to:

 

1. Create a relationship from lineitems to invoices based on invoiceID, and use it to bring the CustomerID (as an unstored calc) into Lineitems.

 

2. Create a relationship from lineitems to customer which is based on customerID (using the calc at 1 above) and use it to reference the pricing indicator.

 

That will get you the required leverage in lineitems to enable you to establish an automated field and relationship driven system for capturing and storing the appropriate price for each transaction.

 

Bear in mind, however, that a number of systems of this type circumvent such problems by retrieving the price value and writing it to the line item by script from the invoice file (from where the relationships to each of the other files can be easily and directly established). Moreover larger systems frequently have a separate price points table which store all prices against date ranges and pricepoint indicators (retail, discount etc), each on a separate record - which offers expanded scope and flexibility when dealing with issues of this type. You may or may not feel that the additional complexity is warranted for your solution.

Link to comment
Share on other sites

Thanks CobaltSky, what is the calculation field that you spoke about supposed to look like in the line items db. I have access to the price level in the invoice table as text file. I have tried to use a calculation before but was unable to get the proper information. You are right about the price fields, they are two seperate fields in the products table. I'm interested in the information you gave about the larger systems and how they set up the db, but I'm not sure I fully understand how they have it set up. In a nut shell, what I'm trying to accomplish is when creating a invoice for a customer, when you add a new lineitem, the calculation field that you spoke about would look at the pricing type (a text field that would be retail or dealer price or free) it then grabs the correct price from the products table (as indicated earlier they are two seperate fields) and brings it into the lineitems db where a lookup is triggered that copies the information via the self relationship that you spoke about. I hope this helps a little bit more. I understand your explanations on what I'm supposed to do, I just don't understand how to do it.

 

Thanks for being patient with me and for all your help.

 

RSB87

Link to comment
Share on other sites

Although I sure ain't a Cobalt Sky, here's my 2¢ worth.

 

I usually have both a wholesale (cost) and a retail (selling) price in my Inventory files (Products, in your case). Simply have them both looked up into your invoice file (actually the line items holding files), but only display the retail price on the invoice layout. The cost price is now in the file for any profit analysis you wish to make.

 

Using a holding file such as line items is very flexible in that each item is a separate record and can therefore, be manipulated in ways that repeating fields cannot. It just takes some getting used to, to understand that your invoice data is actually in another file and most reports will emanate from that holding file. But, with experience, you can create smooth transitions for reports which the end user will not even notice.

 

Tom

Link to comment
Share on other sites

Reread your query and realized what you are trying to do. It's quite simple, really. When a new invoice is created, you probaly draw customer data from a list somewhere. In this list, designate entries as "retail" or "dealer".

 

Then the invoice can be easily programmed to provide the proper price based on the customer classification.

 

Another solution would be when a invoice is created, a request is made to identify whether this is a retail customer or a dealer. If dealer is selected, dealer prices are applied. If retail is selected, than retail prices would automatically apply.

 

I have used this to discern between dozens of price levels on the same goods for different levels of customers.

 

Tom

Link to comment
Share on other sites

Hi rsb87,

You asked for the specific procedure to implement a customer price category dependent lookup in your line-items file which will dynamically reference the appropriate price field in the related record of your products file based on data in a field in your customer file. Here it is, step by step:

 

1. Create a relationship within your lineitems file that connects to the invoice file based in invoiceID.

 

2. Create an unstored calculation in your lineitems file with the formula defined as invoice::customerID

 

3. Create a relationship in your lineitems file which matches the calc (at 2 above) to the customerID field in the customer file.

 

4. If you don't already have one, create a relationship in your lineitems file which matches the productID field to the productID field in the product file.

 

5. Create an unstored number calculation field called 'CustomerPriceCheck' in your lineitems file with the formula specified as:

 

Case(

customer::PriceCategory = "retail", product::RetailPrice,

customer::PriceCategory = "discount", product::DiscountPrice,

customer::PriceCategory = "free", 0)

 

6. Create a stored calculation field (of result type number) called 'cRecordID' in your lineitems file, and specify the formula as: Status(CurrentRecordID)

 

7. Create a calculation field (of result type number) called 'cPriceTrigger' in your line items file, with its formula specified as:

 

Case(not IsEmpty(productID), Status(CurrentRecordID))

 

8. Create a self-join relationship called 'PriceReference' in your lineitems file which matches the cPriceTrigger field to the cRecordID field.

 

9. Define the ItemPrice field in your lineitems file as a lookup to copy values from the CustomerPriceCheck field based on the PriceReference relationship.

 

That's it, you're done. With the above set-up in place, whenever a productID is added to a line item, the appropriate price, as per the customer price specification for the customer on the current invoice, will be looked up from the product file and will appear in the price field in the lineitem record.

 

As noted earlier, because it is a lookup, subsequent changes to the customer price category or the price amounts in the product file will not impact the lineitem price - it will always reflect the values that were in place when the productID was entered into that lineitem record. To 'force' an update, it would be necessary to perform a relookup on the productID field in the lineitems file. cool.gif

Link to comment
Share on other sites

Thanks a million cobaltsky, I finally feel like I'm getting somewhere. Ok, I completed steps 1 through 5 and everything is working perfectly!, but when I try and implement steps 6 through 9:

 

6. Create a stored calculation field (of result type number) called 'cRecordID' in your lineitems file, and specify the formula as: Status(CurrentRecordID)

7. Create a calculation field (of result type number) called 'cPriceTrigger' in your line items file, with its formula specified as:

Case(not IsEmpty(productID), Status(CurrentRecordID))

8. Create a self-join relationship called 'PriceReference' in your lineitems file which matches the cPriceTrigger field to the cRecordID field.

9. Define the ItemPrice field in your lineitems file as a lookup to copy values from the CustomerPriceCheck field based on the PriceReference relationship.

I seem to be having problems with this. When I created the fields, the Status(CurrentRecordID) gave me this really high number (32333) or something similiar. Not sure if that is correct, but the price field is not triggering the look up for some reason. So I tried to change the cPriceTrigger to: Case(not IsEmpty(ProductID), LineItemsID)), but this doesn't seem to work. the ProductID I'm assuming is the Foreign Key from the products table? Any suggestions as to why the lookup isn't triggering?

 

Thanks,

 

RSB87

Link to comment
Share on other sites

Hi rsb87,

First of all, many people are not aware that recordIDs are not issued sequentially in FileMaker. That is why your record ID numbers are 32333 etc. There is an article on the FMI knowledgebase about this:

 

http://www.filemaker.com/ti/104663.html

 

Notwithstanding that, your problem getting the lookup to trigger is a little puzzling. On the face of it the 'top ten' likely suspects would be:

 

1. That the cRecordID field is not stored or not indexed.

2 That the data types of the cRecordID and cPriceTrigger fields are not the same.

3. That one field formula is using a different function from the other - eg Status(CurrentRecordNumber) instead of Status(CurrentRecordID).

4. That you have inadvertently placed quotes around a function or field name in one of the formulae.

5. That the Case( ) expression is not referencing the correct field - or is referencing it via a self-join rather than directly.

6. That you have a space at the start or end of a field name or a relationship name.

7. That you have inadvertently selected the wrong relationship as the basis of the lookup.

8. That you have inadvertently selected a different field on one side of the relationship.

9. That the field you are placing a value in to try to trigger the lookup is not the same productID field referenced in the cPriceTrigger calc.

10. That you have the cRecordID field on the left of the relationship and the cPriceTrigger trigger field on the right, instead of the other way around.

 

I guess there are a few other possibilities, but the above list constitutes a resonable round-up of troubleshooting prospects.

 

The information you've provided doesn't give anything to go on to point to which of these or other possible causes is most likely, so all I can suggest is that you work through the above, 'checklist' style - to see if it will help you to ferret out the 'gremlin' that is preventing the lookup from triggering correctly for you.

Link to comment
Share on other sites

Ok maybe this might help. I was fooling around trying to get this thing to work and I changed the lookup criteria to copy the next lower value if there is not an exact match and it copied a lower value. When I change it back to exact match nothing gets copied? I'm not sure if this helps debug the problem. I have looked over the 10 points that you mentioned and everything seems to be in order.

 

RSB87

Link to comment
Share on other sites

Hi,

If the 'copy next lower' works, then that signifies that the lookup is working, but two calculations in the fields your relationship is matching are not producing the same result.

 

If you've set it up the way I described, both calculations will be generating a result from the Status(CurrentRecordID) function, and if the field types are the same, they must produce matching data - here is nothing else they can produce!

 

I suggest that you take a look at what values both fields are producing, and then check the calculations to find out why they are different?!

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use