Sales Price calculated in queries to be transferred to Sales Invoice table

[Perfac]

Our system include the features below as far as returning the Sales Price.
1. Inventory works on the FIFO basis and our system does that successful.
2. There is a "Business Policy" table that stores the average markup on cost price.


3. Doing it on FIFO basis result that the sales price changes every time the Inventory Item is bought at a different price. Which is right.
4. The user have an option to set Sales Prices manually, and if no price was set our Program establish Sales Price automatically.
5. Challenges that came up include one where Inventory was sold before the Purchase Invoice was posted.
6. There is no medicine for users that doesn't hold discipline but our system must provide for things like this.

Here is my challenge. I am looking for advice how to carry the Sales Price over from the query where it is calculated, to the field in the table that is not calculated. We use a lot of append queries and some update queries, but my ageing mind has not found the way yet. Because the FIFO price changes all the time, if we call up an invoice of a month ago and edit the fields it changes the Sales Price on an old invoice. Even when we carry the price over with an append query or update query, it will carry over the changed price, if we edit fields. The Sales Price must not change when calling up a historical record.

The idea I have right now is to connect the event and use the date as criteria. It will Append or Update the sales price only if the document date is today. Anybody with clever advice?

[kd2017]

Can you post a screenshot of your table relationships view?

When you record an sales order/invoice the sales price should be copied to or recorded with the order and not just linked back to some other item table. Is this how you have it set up? This is how you would be able view old invoices and maintain a historical record. Or is this the gist of what you're asking? What code have you tried that's not working as expected? You can use update queries and/or vba to do this but we'll need more specific information about your app itself to give specific answers.

What do you mean by "no medicine for users that doesn't hold discipline"? Is this in reference to point #5? Can you just lock down your application to enforce business rules?

[Perfac]

Thank you. I will try to make a long story short. In a complicated query I have successfully arrived at a field ActualSalesPrice. In the table SalesInvoice there is a field UnitPrice. If I Update "ActualSalesPrice" to "UnitPrice" it works all right for that moment. "ActualSalesPrice" will change all the time. Opening a historical invoice will still cause no problem. Editing the fields in the historical invoice will change the historical invoice, because the event is "AfterUpdate" of the Inventory Item field, then the "Update Query" updates, and changes the invoice to the current price. The short story is how to transfer the "ActualSalesPrice" to "UnitPrice" once. Returning to the historical invoice must not update it again. As I mentioned in thread 1 that will work, but if dates go wrong on a users computer, it will not work. I accept that if dates on computers are wrong then the user can enter the SalesPrice manually.

What I meant with "no medicine". Admin process is as such that users should process a Purchase Invoice at the moment of delivery, and Inventory should not be sold, because if it is policy that our system adjust prices the moment that invoice is processed. FIFO is the most logic way to establish prices. If a business sells Inventory and a SalesInvoice is issued before the Purchase Invoice was processed, they didnt follow procedure, and the price will be a little wrong. Our process will still flow and all is OK.

We have a Sales Order Form, and a Sales Quotation, where those two work very similar than the Sales Invoice. We still need work for the order system to manage and report availability, and if I fix my challenge on the Invoice, I have to do it here as well.

[Perfac]

I have added an expression that will only Update when the "DocumentDate" = "Today". That will do the job, even though there is a small gap for it to still go wrong.

[kd2017]

Can you post a screenshot of your table relationships view? If we can get a picture of your data model it'll be easier to help you.

You want to to update [SalesInvoice].[UnitPrice] to equal [ComplicatedQuery].[ActualSalesPrice], correct? Currently the problem is the update updates all invoices but you just want to update a specific [SalesInvoice]?

IF thats the case it should be a trivial update query but we still need additional information about your data model to give you accurate code.

Does your [SalesInvoice] have just one item or can it have many items? Does [ComplicatedQuery].[ActualSalesPrice] return multiple rows or just a price for one item? In other words are you trying to update many items on a [SalesInvoice] at once or just one item at a time?

Is it form button that you're clicking that's supposed to run this update or how is it being executed?



Generic code to update all the unit prices in an invoice:

UPDATE
    SalesInvoiceItems
SET
    SalesInvoiceItems.UnitPrice = ComplicatedQuery.ActualySalesPrice
FROM
    SalesInvoiceItems
INNER JOIN
    ComplicatedQuery
ON
    SalesInvoiceItems.ItemID = ComplicatedQuery.ItemID
WHERE
    SalesInvoiceItems.SalesInvoiceID = [SALES INVOICE ID]

[Perfac]

It seems you understand me, except it doesn't update all invoices. The VBA is set on event. It updates only when the historical invoice is opened, and the quantity field or Inventory Item (SubLdgName) on the image is changed. Even if the same quantity is reentered the unit price will highly likely change, when Inventory was purchased at different prices.
My system got 1400 objects. I put only three on the view. You can see the Invoice sub, so it can store many records on one invoice. What I suggested above I applied in the last hour, it works creating a new invoice, and it doesn't change historical invoices. I am suppose to be an auditor as well, and documents posted in the past may not be changed, so that is the result here and that should be perfect. But as a businessman I don't like corrections in my accounts, against GAAP. I like if an error was made yesterday on an invoice, to edit the invoice and fix it. I assume there is a way for what this thread asks for, but if not. This solution that came up is actually the best.

If there is a way to transfer the ActualSalesPrice to UnitPrice in the table t02SalesInvoiceSub and thereafter, returning to this invoice, should not change the unit price. The expression is on the form and not in the query, but that still means Unitprice004 is not open to manually put a price in. If that is possible, it is what this was all about.

[Perfac]

If all invoices were posted on the day it should have, then what I suggested work, but users definitely will want to post invoices of days before. My suggestion is not good. Must find a way. I assume there is VBA that can do what I need.

[kd2017]

Perfac,
I do really want to help you. At this point I'm totally invested into helping you solve your problem! Please have patience with my continuing questions.

So when you open the Sales Invoice Input form (image #3 above) and change [Sub Ledge Name] OR [Qty] in it's subform then some VBA executes and automatically updates [UNIT PRICE] and this is undesired behavior?

I'm assuming you are not the original developer of this database application?

If there is a way to transfer the ActualSalesPrice to UnitPrice in the table t02SalesInvoiceSub and thereafter, returning to this invoice, should not change the unit price. The expression is on the form and not in the query, but that still means Unitprice004 is not open to manually put a price in. If that is possible, it is what this was all about.

Please rephrase this paragraph, I simply don't understand what you're saying. But I do have a feeling what you want is certainly possible!

[Perfac]

Thanks. At 02h00 this morning, the solution was not with me yet, I think it is now. I am the original developer with my son Ruben(23) who is qualified. In 2018 we spent 16 months on this only, and as a business man for 32 years I had an obsession to build this, paid other people which didn't succeed. I am excited about it so far, but a lot to do still. Lying in bed the solution came to me as follows. You may have noted in my query there are fields for "PolicySalesMarkup", and "ManagementAdjustment". Thoughts are.

1. Add a second field in t02SalesInvoiceSub "UnitPrice2" which is update able.
2. Add a button on the Sales Invoice "Set Unit Price manually". Ruben taught me how to make fields visible or not visible. Clicking on this button will make UnitPrice004 not visible and Unitprice2 visible. The experession will use the manually set price, ignoring the aut calculation.
3. As far as the actual Unit price there will be three fields now. ActualSalesPrice in the query. This new field UnitPrice2 and UnitPrice004 that now returns the answer to the new expression.
3. VBA on the form will use an iif Statement. UnitPrice004 = iif(UnitPrice2 > 0, UnitPrice2, ActualSalesPrice.)
4. I always wanted for users with 100's or 1000's inventory items that sales prices will be calculated automatically with an option to set it manually. Do you think it is the ultimate?
5. If users follow discipline and process purchase invoices at delivery and don't sell Inventory before the Purchase Invoice was processed, it work. There will always be human error.
6. If Inventory are sold before the Purchase Invoice were posted the automatic SalesPrice will be wrong, and the system must provide for that, especially when calling up a historical invoice.
7. Another change, dates on queries will be set that if a historical invoice is called up, it will return the correct FIFO price on that day.
8. On our system, if a user calls up a historical invoice, no problem, but editing the quantity field or Inventory field(SubLdgAcc) will now not return the wrong price any more. Only in the unlikely event of the user selling Inventory before the Purchase Invoice was processed things could go wrong. If that can the be corrected manually, all is well. I do not sense another possible solution.
9. You may note on the Invoice Image there is a button "Enable". Only someone with administrator rights can edit and change an existing invoice.
10.Two years ago we set up the system to create an invoice in one table with all calculations in the query, then save it to a table with no calculations, but a lot of problems arose. It could work but it made things tough.

Many times, advice meant a lot, and thank you for the effort, if you think of something please tell me.

[Perfac]

Attachment 41450
Sales Invoice Input Form.

[CJ_London]

If I can try to distil the problem.

Are you saying the issue is that someone creates an invoice but does not 'post' it into the ledger, i.e. it is a pro-forma invoice. The sales price on that invoice is based on the FIFO cost at the time it was created. If the user then goes back into the invoice and makes some changes (perhaps at a later date), then because the FIFO value has changed, the invoice sales price gets updated. And you want it to remain the same?

[kd2017]

If what ever you've come up with works then that's great! Another approach to consider might be instead of having multiple [Unitprice] fields that you could have an optional field in the SalesInvoiceSub table that is the User ID of the person working on the invoice, call it OverrideUserID for example. If [OverrideUserID] Is Null then your app would be designed to automatically calculate and update [SalesInvoiceSub].[Unitprice004], however if it is not null then the app would skip updating the price for this item because the calculated price is 'overridden'. The sql to update the unit prices for an invoice while skipping overridden items might look like this:

UPDATE
    t02SalesInvoiceSub
SET
    t02SalesInvoiceSub.UnitPrice004 = q04CogsUnitPrice02.Unitprice011
FROM
    t02SalesInvoiceSub
INNER JOIN
    q04CogsUnitPrice02
ON
    t02SalesInvoiceSub.[ITEM ID HERE] = q04CogsUnitPrice02.IntItmID011
WHERE
    t02SalesInvoiceSub.SalInv_ID004 = [SALES INVOICE ID HERE]
AND
    t02SalesInvoiceSub.[OVERRIDE USER ID] Is Null

[Perfac]

Hi Ajax. All our documents that influence the Financial statements combines in a Union Query, which results in the ledger. When a SalesInvoice is created in our system it links with the ledger, it is not "posted", just stored in t02SalesInvoice and t02SalesInvoiceSub. No it is not a pro forma invoice. On the one and only form for the SalesInvoice all the calculations needed to return all the numbers VAT,discount, unit price and total. It creates invoices perfectly for a long time. After it is created, it is just there with all the many invoices created before.

To me the ultimate in a system as far as the Sales Invoice is concerned for a retail business will include the following.
1. Sales Prices must be done automatically. In our system, there is a BusinessPolicy table that stores among other the SalesMarkup Percentage.
2. Complicated queries returns the exact Fifo cost price, at the moment of creating a SalesInvoice it calculates the (Sales) unitprice correct as it should.
3. Reality is the big virus you call "Human Error". Purchase Invoices will be processed making mistakes, or they will be processed delayed. Not with all businesses but with some.
4. That will cause the Salesprice to go wrong. Calculations on the SalesInvoice only happens if fields are edited on one open invoice. Calculations will not change any records at the time of fixing a human error on the Purchase Invoice. It will only change one invoice when opened and specific fields are edited.
5. These errors are exceptional, do not happen frequently, but it does. I choose to be able to fix an error made yesterday today by just opening an invoice and set it right. Rules that support GAAP does not want that. One should issue a credit note and process a new SalesInvoice. It all makes accounting and programming much more complicated, and many "corrections" are not welcome in my books. If a capturer yesterday punched in 34 quantity in stead of 43, I like to open the invoice and fix it. If a sales Invoice was done in the meantime, the salesprice will be wrong. If the error is minor, no need to bother the customer, but the invoice must be exactly like the customer got it.
6. Understand that in the normal event when no errors were made, we can edit historical invoices and edit fields and the invoice will stay the same, no problem.
7. It is when errors were made and CORRECTED. If we change the quantity or unit price of a purchase invoice of two days ago, and in the mean time a salesinvoice was done, the salesprice will be wrong on that salesinvoice.
8. The solution in my last thread will work, because the salesprice can be set manually. It makes the perfect process I believe in, work, but I am only 95% happy. I will bear with that if no better way is found.
9.Access is great , I assume there is a way to get the result. My issue is that our system is not Perfect if a user opens a historical invoice and calculations changes anything. Some way of appending or updating and have the Sales Invoice not re-calculating the unit price will solve it.

[Perfac]

Another idea that crossed my mind in the last minute. The Max expression can establish the latest invoice number, then the calculations on the fields "UnitPrice" should not happen if the invoice number is smaller than the current invoice. Or am I nuts? If ever a previous invoice is opened, even the second to last one, it will not recalculate the unit price, that will work.

[kd2017]

FYI the form you attached earlier is invalid so we can't download it.

So the problem is that your Sales Invoice Input form is automatically updating the unit prices on an invoice any time any invoice is opened????