If you have questions or if you want to share your opinion about Aware IM post your message on this forum
#50540 by weblike
Sat Mar 09, 2019 6:36 pm
I'm curious how others are doing DUPLICATE records.

I have Invoice BO which has Invoice.Series as reference .
On each new Invoice the user can select from multiple Series(dropdown)-> and Series.CurrentNumber is incremented by 1.
I want to duplicate one Invoice for a specific Customer-> the user to be capable to select any Series, but on duplicated Invoice the Customer data (name, address) should be cloned form old invoice.

Thank you.
#50548 by ACDC
Mon Mar 11, 2019 6:16 pm
I haven't given much time to your explanation but this example is how I Create an Invoice from a Quote, both the objects are almost the same, so for your purpose you need to change the reference in the example to Quote to Invoice and QuoteItems to InvoiceItems
Also because its the same object i.e Invoice to Invoice, you may have to use "This" and "That" in the setup


Process No1
--------------------------------------
Rule 1
CREATE Invoice WITH Invoice.Account=Quote.Account,
Invoice.Description=Quote.Description,
Invoice.Contact=Quote.Contact,
Invoice.Quote=Quote,

Rule 2
FIND QuoteItem WHERE QuoteItem IN Quote.Items

Rule 3
CreateInvoiceItemWithQuoteItem (this is a process see contents below)
----------------------------------------------
Process No 2: (Invoice and QuoteItems are set as process inputs on the process)

Rule 1
CREATE InvoiceItem WITH InvoiceItem.Invoice=Invoice,
InvoiceItem.ProductCode=QuoteItem.ProductCode,
InvoiceItem.Quantity=QuoteItem.Quantity,
InvoiceItem.SellingPrice=QuoteItem.SellingPrice,
InvoiceItem.Description=QuoteItem.Description,
InvoiceItem.Discount=QuoteItem.Discount,
InvoiceItem.DeliveryCharge=QuoteItem.DeliveryCharge,
InvoiceItem.TaxPercentage=QuoteItem.TaxPercentage,
InvoiceItem.Notes=QuoteItem.Notes,
InvoiceItem.QuoteItemId=QuoteItem.ID


Hope that makes sense and lets you find a way
#50549 by weblike
Mon Mar 11, 2019 6:24 pm
Thank you for your reply.
I have tried to use This That and the process to have the input of "Invoice", but what happens is Series.CurrentNumber becomes "null" in DB.

My process is like this:
Rule1
FIND Series WHERE Series.DefaultSerie='Yes'

Rule2
If SEARCH_COUNT>0 Then
ENTER NEW Invoice WITH Invoice.Serie=ThisSeries USING 'Select Series'


Rule3
Invoice.Sequence_No=Invoice.Serie.CurrentNumber


Rule4
INCREASE Invoice.Serie.CurrentNumber BY 1


Rule5
EDIT Invoice USING Main
#50550 by ACDC
Mon Mar 11, 2019 6:39 pm
I would try remove this from your process "INCREASE Invoice.Serie.CurrentNumber BY 1"
and place the rule on the Object

If Invoice IS NEW then
INCREASE Invoice.Serie.CurrentNumber BY 1
(or whatever condition needs to apply)
#50551 by Jaymer
Mon Mar 11, 2019 6:49 pm
george,
i took a different approach.
i create the new invoice header for the customer, and then copy lines from existing. I let the use PICK FROM to select the older invoice.

i think it was too much problems when i tried it the original way.
problems with THIS and THAT just did not work - after many hours, and maybe thought it was Aware bug, I gave up and tried my different approach.
now am happy

jaymer...
#50555 by weblike
Tue Mar 12, 2019 8:19 am
ACDC wrote:I would try remove this from your process "INCREASE Invoice.Serie.CurrentNumber BY 1"
and place the rule on the Object

If Invoice IS NEW then
INCREASE Invoice.Serie.CurrentNumber BY 1
(or whatever condition needs to apply)

I had this line in Object before, but that lead to duplicate invoice numbers when 2 or more users were creating in same time.
Since I moved this line in process the duplicate numbers are gone.

Jaymer wrote:george,
i took a different approach.
i create the new invoice header for the customer, and then copy lines from existing. I let the use PICK FROM to select the older invoice.

i think it was too much problems when i tried it the original way.
problems with THIS and THAT just did not work - after many hours, and maybe thought it was Aware bug, I gave up and tried my different approach.
now am happy

jaymer...

Yes the This That thing is above my understanding, sometimes is doing right sometimes not (in my case at least).
I have struggled with this process which sometimes works sometimes not:

Code: Select allFIND Series WHERE Series.DefaultSerie='Yes'
If SEARCH_COUNT>0 Then ENTER NEW Invoice WITH ThatInvoice.Serie=ThisSeries USING 'Select Series'
ThatInvoice.Sequence_No=ThatInvoice.Serie.CurrentNumber
ThatInvoice.Customer=ThisInvoice.Customer
INCREASE ThatInvoice.Serie.CurrentNumber BY 1
EDIT ThatInvoice USING Main

Selection_451.png
Selection_451.png (23.3 KiB) Viewed 1380 times
#50556 by ACDC
Tue Mar 12, 2019 10:46 am
I had this line in Object before, but that lead to duplicate invoice numbers when 2 or more users were creating in same time.
Since I moved this line in process the duplicate numbers are gone.


If you put it back on the object does it work ?
#50557 by weblike
Tue Mar 12, 2019 12:27 pm
ACDC wrote:
I had this line in Object before, but that lead to duplicate invoice numbers when 2 or more users were creating in same time.
Since I moved this line in process the duplicate numbers are gone.


If you put it back on the object does it work ?

Same result.
The problem with Process above is that, no matter what Invoice I select to duplicate it puts the same Customer on it even the Input->Invoice has other customer.
The Serie is ok.
#50569 by Markfre1
Fri Mar 15, 2019 4:44 am
I don't know why I didn't look on the forum earlier. I was using the Duplicate function which duplicated the header perfectly and bumped up the invoice # automatically. I used the Duplicate function to duplicate the lines and that seems to work perfectly. Then as I did more, on certain invoices it would get the wrong lines. Since the docs are scant on this, I thought I was doing it incorrectly and tried for two hours. Using the Duplicate function is 2 lines of code (one for header and one for lines), so I sent to support my code to see if I am doing it incorrectly. Otherwise, I guess I have to read the lines of the old and create the new?

Mark F
#50683 by Markfre1
Sun Apr 07, 2019 6:03 am
I wasn't able to read all the posts here, but you can use the 'Duplicate' to duplicate the header and references. You can use NO RULES statement and assign attributes using This and That. Or you can Duplicate with rules, using the Except xxxatribute. For example, I Duplicate an Invoice Header which is a Quote, assign InvoiceType to ThatInvoiceType='Invoice' and use with 'Except' Invoice #. I have an object with the last Invoice # used, and update the invoice # and last used #. Then I duplicate the line items. This works.

Also, I do not use auto-increment because I need the option for the user to be able to override an Invoice #. If the next is 105 but they previously manually entered 105, it bumps up through 105 to the next available. If 106 and 107 were used, then it would increment to 108. I use the exists statement and this executes until an available number is hit. This works and should not have any multi-user issues.

The problem I have is I also do recurring invoices. And It only bumps up to the next number. But if that # was used for some reason, it won't skip to the next available # like it would if I was entering a new invoice. Because I am using the Rules in the main object, after calling the duplicate process, it should execute the rules of the object and work. Not sure why the rules won't execute in the same way as a 'New'.

So basically I am keeping track of the last # used, or entered, increment from that point, but if the incremented # is an existing one, it jumps up to the next and so on.

FreshBooks does this. QuickBooks actually lets you create duplicate invoices numbers which is absurd and Xero is just weird. Any suggestions appreciated. Aware_Support suggested a stored procedure, but I not sure how this would work.

Mark F
#50684 by ACDC
Sun Apr 07, 2019 8:12 am
With regards to invoice numbering, I use the Object.ID as part of the number formula using (IF NEW), this ensures uniqueness and I dont have to have other rules managing the number

So if you could use the Invoice.ID as the invoice number it would simplify things or a rule that concatenates the Invoice.ID with the sequence number that's being incremented. e.g : 1290-106. If the number after the dash is duplicated it does not matter as it still remains unique.

If the user wishes to change (override) the number, that's fine, just check if it does not exist
#50696 by Markfre1
Thu Apr 11, 2019 2:45 am
Thanks for the info. So this is a commercial mass market app for small businesses. We have found with our C/S that some companies want to enter their own, some want to have auto increment, and some do a combo of both. Also, I have to take in consideration the tenant since it one database for all tenants.

I was able to get this to work for both entering new invoices, duplicating invoices and recurring invoices. I store the last Invoice no used by the tenant. When creating an invoice whether it be from any of the 3 methods, I use the Exist statement, to check if it exists. If it exists, I bump up one, until the statement is false. Then I update the last invoice no as well.

The result is if I have someone entered invoices 102, 104, 106, and I add an invoice, it will add 103, then 105, 107, etc.
I created this in the rule of the object. So the Duplicate Invoice except Invoice no, creates it, and then the rules bump it up and assign the correct invoice no.

Freshbooks does it this way. QuickBooks online lets you have duplicate invoice #'s which is absurd. Xero is a mishmash. So it seems to work fine, but will need extensive testing in a multi-user environment.

Thanks Mark F
#50697 by PointsWell
Thu Apr 11, 2019 6:43 am
Markfre1 wrote:The result is if I have someone entered invoices 102, 104, 106, and I add an invoice, it will add 103, then 105, 107, etc.
I created this in the rule of the object. So the Duplicate Invoice except Invoice no, creates it, and then the rules bump it up and assign the correct invoice no.


Don't know where your customers are and my comments are only from what I have found working with EU countries. There are strict rules in place with regards to the use of invoice numbers for them to qualify as tax invoices - the alarm bell rang for me when you mentioned invoices being created with numbers out of sequence. The UK's HMRC states at https://www.gov.uk/hmrc-internal-manual ... vatrec5020
The ‘invoice number’ can be numerical, or it can be a combination of numbers and letters, as long as it forms part of a unique and sequential series.


This theme is repeated a number of times not just in the the VAT for intra EU sales manual (this was just the first that came to hand).
#50705 by Markfre1
Thu Apr 11, 2019 4:08 pm
Very interesting. Xero is trying to make inroads in the US market and have to review again what they do. They let you create a number but they also create a number like INV-001. Need to go back and exactly see what they are doing and why (maybe that's why I thought it was gobbledygook). Freshbooks has expanded out of the US to certain markets, so I should look at the countries they expanded to and see what the rules are. Maybe that is why they are not in all countries?

Here in the US, you can use anything. Just using a sequential # is really easy; except not in the case of Multi-tenant with one BSV. I need to auto-increment by tenant But with the method developed, I can just make the invoice # a protected field based on a country flag.

The US market is big enough, but who knows, so I will build that in.

Thanks for the input.

Mark F

Who is online

Users browsing this forum: Google [Bot] and 11 guests