MYOB Export.

[r31gnf1r3]

Have to be able to export all invoices based on date range, or single items based on Invoice ID. or entire system.

Things to import:

• Customer Cards
• Inventory Items
• Item Sales

This is done using CSV in MYOB format. Also note there is different formats depending on MYOB, so it will need an option on the export choosing which format. They are using MYOB AccountRight Plus v19.9

Here's an explaination from MYOB on the Import/Export Field types: http://help.myob.com.au/acct19/win/impexptables.htm#4818184

Here's a guide on Resolving import errors: http://myobaustralia.custhelp.com/app/answers/detail/a_id/13280

Here's what the process looks like on the MYOB end: http://www.mole-end.co.uk/acatalog/1StopMYOB.html http://help.peppercan.com/2012/01/import-sales-invoices-into-myob/

[zvadym]

@r31gnf1r3 Please provide CSV example

[r31gnf1r3]

http://community.myob.com/t5/Getting-started-with/Importing-Item-Sales/td-p/111418

On Sun, Jun 1, 2014 at 3:08 PM, Vadim notifications@github.com wrote:

@r31gnf1r3 https://github.com/r31gnf1r3 Please provide CSV example

— Reply to this email directly or view it on GitHub https://github.com/the0forge/sp/issues/7#issuecomment-44767831.

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[r31gnf1r3]

http://community.myob.com/t5/Getting-started-with/Importing-CSV-or-Tab-Delimited-file-error/td-p/69039

On Mon, Jun 2, 2014 at 2:28 AM, Stevo Gibbs stevo.gibbs@gmail.com wrote:

http://community.myob.com/t5/Getting-started-with/Importing-Item-Sales/td-p/111418

On Sun, Jun 1, 2014 at 3:08 PM, Vadim notifications@github.com wrote:

@r31gnf1r3 https://github.com/r31gnf1r3 Please provide CSV example

— Reply to this email directly or view it on GitHub https://github.com/the0forge/sp/issues/7#issuecomment-44767831.

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[r31gnf1r3]

They are using: MYOB AccountRight Plus v19.9 ~ SingleUser License. I'll get some export CSV's for you when I go into the office.

On Sun, Jun 1, 2014 at 3:08 PM, Vadim notifications@github.com wrote:

@r31gnf1r3 https://github.com/r31gnf1r3 Please provide CSV example

— Reply to this email directly or view it on GitHub https://github.com/the0forge/sp/issues/7#issuecomment-44767831.

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[zvadym]

I need clear instructions. Like

file1.csv with columns col1;col2;col3

where col1 = Product.current_stock col2 = Order ....

[r31gnf1r3]

Emailed customer already for a working example of an export from the MYOB software.

On Mon, Jun 2, 2014 at 4:28 PM, Vadim notifications@github.com wrote:

I need clear instructions. Like

file1.csv with columns col1;col2;col3

where col1 = Product.current_stock col2 = Order ....

— Reply to this email directly or view it on GitHub https://github.com/the0forge/sp/issues/7#issuecomment-44805371.

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[zvadym]

So this is columns list, right?

Co./Last Name   
First Name  
Card ID 
Addr 1 
- Line 1               
- Line 2               
- Line 3               
- Line 4               
- City             
- State            
- Postcode             
- Country              
- Phone # 1            
- Email            
- Contact Name

This report is based on Invoice model, right?

class Invoice(models.Model):
    """ An Invoice for an Order issued by a particular Company that SmartPractices trades as
    """
    order = models.ForeignKey(Order, related_name='invoices')
    company = models.ForeignKey(Company, related_name='+')
    number = models.PositiveIntegerField()
    timestamp = models.DateTimeField(default=datetime.now, auto_now_add=True)

[r31gnf1r3]

SERVICE SALE Conditional Fields Are (need one or more to link up with other records):

1. Card ID - 15 characters, alphanumeric. Must be a unique ID. Note: When you select Card ID in the Identify Card by field, *None is equivalent to no entry in the field.
2. Record ID - 10 numeric characters. The Record ID is an internal number within the company file
3. Co./Last Name - When importing, must match a pre-existing card in the company file. If a company name (no first name), 50 characters, alphanumeric. If a last name, 30 characters, alphanumeric. NOTE: To match a pre-existing card designated as “Individual”, your software requires both the last and first names

Required Fields Are (these have to be in for an import to work):

1. Account # - 5 characters, numeric. Must be valid, pre-existing account number. May have an optional non‑numeric separator between the first digit and the last 4 digits (example: 1‑1234).
2. Amount - 15 characters (including 2 decimal places). More than 2 decimal places are rounded to 2 decimal places.
3. Inc-Tax Amount - 15 characters (including 2 decimal places). More than 2 decimal places are rounded to 2 decimal places.

CUSTOMER Conditional Fields Are (need one or more to link up with other records):

1. Co./Last Name - If a company name (no first name), 50 characters, alphanumeric. If a last name, 30 characters, alphanumeric.
2. Card ID - 15 characters, alphanumeric. Must be a unique ID. Note: When you select Card ID in the Identify Card by field, *None is equivalent to no entry in the field.
3. Record ID - 10 numeric characters. The Record ID is an internal number within the company file.

There are NO Required Fields for Customer.

[zvadym]

@r31gnf1r3 can you please attach an example

[r31gnf1r3]

With this modal dialog popup. On main-window, top-menu, have new menu item on the right (next to reports) called 'export'. Then a drop down menu option called 'MYOB'.

The modal pop-up goes:

• [ ] Text title 'Data type:' Then a Dropdown with the Choices as: Customers OR Sales

IF Customers chosen in 'Data type' dropdown, then just below that dropdown options are:

• [ ] Text title 'All Customers:' Checkbox True/False boolean (true by default)
• [ ] Text title 'Customer IDs' Textbox (disabled by default)
• [ ] Export button

IF 'All Customers' is unchecked (false) THEN

• [ ] Text title 'Customer IDs' enabled, its a comma and/or space seperated list of customer ID's to export.

IF Sales chosen in ' Data type' dropdown, the just below that dropdown options are:

• [ ] Text title 'Export By:' Dropdown with the Choices "Today, This Week, This Month, 3 Months, YTD (Year to Day)"
• [ ] Export button

[r31gnf1r3]

Icon for main-menu dropdown for MYOB option.

[zvadym]

@r31gnf1r3 please check it, I'm trying to define the fields in the report.

Very important. If you give me a clear answer I will close this task

Customers: (model: Customer)

Co./Last Name    = {{ ob.name }}
First Name = empty because {{ ob.name }} contain it
Card ID = {{ ob.from_src_company_id|default:"*None" }}
Addr 1 - Line 1 = {{ ob.address_line_1 }}
- Line 2 = {{ ob.address_line_2 }}
- Line 3 = empty
- Line 4 = empty
- City = {{ ob.suburb }}
- State = {{ ob.state }}
- Postcode = {{ ob.postcode }}
- Country = {{ ob.country }}
- Phone # 1 = {{ ob.telephone }}
- Email = {{ ob.email }}
- Contact Name = empty

please check Card ID field. Am I right?

Sales (model: Order)

Co./Last Name = {{ ob.customer.name }}
First Name = empty
Addr 1 - Line 1 = {{ ob.customer.address_line_1 }}
- Line 2 = {{ ob.customer.address_line_2 }}
- Line 3 = empty
- Line 4 = empty
Invoice # = {{ ob.last_invoice.number }}
Date = {{ ob.order_date|date:"d.m.Y" }}
Customer PO = empty
Account # = {{ ob.customer.pk }}
Amount = ???
Inc-Tax Amount = ???
Tax Code = GST
Non-GST Amount = ???
GST Amount = ???
Freight Amount = ???
Card ID = {{ ob.customer.from_src_company_id|default:"*None" }}
Record ID = {{ ob.from_src_order_id|default:"*None" }}

please check CardID and RecordID. And I can`t understand what I have to use for ???

[r31gnf1r3]

CardID and RecordID are conditional fields, meaning we don't need to use them as long as with have one of the other conditional fields.. (i.e. We only need one conditional field, we can use more then one, but only one is required) and since we don't know the internal CardID or RecordID of MYOB, I think we should ignore (i.e. not use them) for our first round of testing... as I can't see how we could work them out.

On Thu, Aug 21, 2014 at 12:28 AM, Vadim notifications@github.com wrote:

@r31gnf1r3 https://github.com/r31gnf1r3 please check it, I'm trying to define the fields in the report.

Very important. If you give me a clear answer I will close this task

Customers: (model: Customer)

Co./Last Name = {{ ob.name }} First Name = empty because {{ ob.name }} contain it Card ID = {{ ob.from_src_company_id|default:"*None" }} Addr 1 - Line 1 = {{ ob.address_line_1 }}

• Line 2 = {{ ob.address_line_2 }}
• Line 3 = empty
• Line 4 = empty
• City = {{ ob.suburb }}
• State = {{ ob.state }}
• Postcode = {{ ob.postcode }}
• Country = {{ ob.country }}
• Phone # 1 = {{ ob.telephone }}
• Email = {{ ob.email }}
• Contact Name = empty

please check Card ID field. Am I right?

Sales (model: Order)

Co./Last Name = {{ ob.customer.name }} First Name = empty Addr 1 - Line 1 = {{ ob.customer.address_line_1 }}

• Line 2 = {{ ob.customer.address_line_2 }}
• Line 3 = empty
• Line 4 = empty Invoice # = {{ ob.last_invoice.number }} Date = {{ ob.order_date|date:"d.m.Y" }} Customer PO = empty Account # = {{ ob.customer.pk }} Amount = ??? Inc-Tax Amount = ??? Tax Code = GST Non-GST Amount = ??? GST Amount = ??? Freight Amount = ??? Card ID = {{ ob.customer.from_src_company_id|default:"_None" }} Record ID = {{ ob.from_src_order_id|default:"_None" }}

please check CardID and RecordID. And I can`t understand what I have to use for ???

— Reply to this email directly or view it on GitHub https://github.com/the0forge/sp/issues/7#issuecomment-52785649.

---

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.

[zvadym]

OK, and what about

• Amount
• Inc-Tax Amount
• Non-GST Amount
• GST Amount
• Freight Amount

?

[r31gnf1r3]

If NAMEFIELD meets the following requirements:

1. {FIRSTNAME} {LASTNAME}
2. {DR MR MASTER MRS MISS MS SIR MADAM PROF} {FIRSTNAME} {LASTNAME}

EXAMPLE: John Doe, OR: Dr Jane Doe, then we use the Firstname & Lastname fields.

ANYTHING ELSE: Just the whole Field goes into the Co./Fistname Field.

Need options model:

name - text description - text value - text type - text default - text

Add into options model:

myob_force_company : dont use lastname fields, put it all into Co./Firname : false : boolean : false myob_account : internal myob account to export to : 41001 : number : 4000 myob_tax_code : internal myob tax code : GST : text : GST myob_tax_exempt_code : internal myob tax exempt code : EXP : text : EXP myob_use_addr_only : dont use the city/state/postcode fields, addr only : true : boolean : false myob_use_name_in_addr : the Line 1 of Addr contains customers name : true : boolean : false myob_force_id_none : make card ID and record ID value *None : false : boolean : false myob_use_id : export using our IDs for card ID and record ID (not recommended) : false : boolean : false

Those options will enable us to export using the possible options depending on the MYOB configuration.

How it works.

myob_force_company option, this option means that we don't try to workout first and last names, and just put the entire field into the firstname field.

myob_account option, this is the internal account used within MYOB where service sales are, by default its 4000, but for their current setup on MYOB it is 41001. Account numbers get changed and reconfigured etc over time.

myob_tax_code option, this is the code used to identify the tax within MYOB. If we have TAX/GST applied (i.e. a sale in Australia) then this code is used. And tax is also applied.

myob_tax_exempt_code option, this is the code used to indentify sales items without tax. If there is no tax, this is the code used. In their case its EXP.

myob_use_addr_only, normally in MYOB you used the Country, State and Postcode fields, BUT it depends on how they have configured their MYOB. In the case of smartpractice, they are using the Addr Line 1, Line 2, Line 3 and Line 4 fields. Not Country, State and Postcode.

myob_use_name_in_addr option, use the customer name in the Line 1 field and bump Line 1 to Line 2, Line 2 to Line 3.

[zvadym]

@r31gnf1r3 what do you think about it?

https://github.com/the0forge/sp/blob/f77d77d3a560da1226cd638a41fda215458b0ec8/frontend/models.py#L139

    @property
    def parsed_name(self):
        try:
            force_company = bool(Settings.objects.get(key='myob_force_company').value)
        except Settings.DoesNotExist:
            pass
        else:
            if force_company:
                return {
                    'l': self.name,
                    'f': ''
                }

        data = self.name.split()
        if data[0].upper() in ('DR', 'MR', 'MASTER', 'MRS', 'MISS', 'MS', 'SIR', 'MADAM', 'PROF'):
            data = data[1:]

        if len(data) != 2:
            return {
                'l': self.name,
                'f': ''
            }
        return {
            'f': data[0],
            'l': data[1]
        }

[zvadym]

instead options model I have used https://github.com/zvadim/django-db-settings - my old module.

[zvadym]

I still have questions about SALE report

We have the following columns:

1. Co./Last Name
2. First Name
3. Addr 1 - Line 1
4. - Line 2
5. - Line 3
6. - Line 4
7. Invoice #
8. Date
9. Customer PO
10. Account #
11. Amount
12. Inc-Tax Amount
13. Tax Code
14. Non-GST Amount
15. GST Amount
16. Freight Amount
17. Card ID
18. Record ID

And this is the data that I want to insert there.

1. {{ ob.customer.parsed_name.l }}
2. {{ ob.customer.parsed_name.f}}
3. {{ ob.customer.address_line_1 }}
4. {{ ob.customer.address_line_2 }}
5. 
6. 
7. {{ ob.last_invoice.number }}
8. {{ ob.order_date|date:"d.m.Y" }}
9. 
10. {{ db_settings.myob_account }}
11. 
12. 
13. {{ db_settings.myob_tax_code }}
14. 
15. 
16. 
17. {{ ob.customer.from_src_company_id|default:"*None" }}
18. {{ ob.from_src_order_id|default:"*None" }}

Please check it and tell me what to do with empty lines..

I want to remind you that the order has the following amount properties:

@property
def summary(self):
    data = {
        'discount': ...,
        'tax': ...,
        'sub_cost': ...,
        'sub_price': ...,
        'sub_profit': ...,
        'gross_cost': ...,
        'gross_price': ...,
        'gross_profit': ...,
        'net_cost': ...,
        'net_price': ...,
        'net_profit': ...,
        'qty': 0
    }
    return data

[r31gnf1r3]

1. Co./Last Name
2. First Name
3. Addr 1 - Line 1
4. - Line 2
5. - Line 3
6. - Line 4
7. Invoice #
8. Date
9. Customer PO
10. Account #
11. Amount
12. Inc-Tax Amount
13. Tax Code
14. Non-GST Amount
15. GST Amount
16. Freight Amount
17. Card ID
18. Record ID

And this is the data that I want to insert there.

1. {{ ob.customer.parsed_name.l }}
2. {{ ob.customer.parsed_name.f}}
3. {{ ob.customer.address_line_1 }}
4. {{ ob.customer.address_line_2 }}
5. 
6. 
7. {{ ob.last_invoice.number }}
8. {{ ob.order_date|date:"d.m.Y" }}
9. 
10. {{ db_settings.myob_account }}
11. 
12. 
13. {{ db_settings.myob_tax_code }}
14. 
15. 
16. 
17. {{ ob.customer.from_src_company_id|default:"*None" }}
18. {{ ob.from_src_order_id|default:"*None" }}

So lines 5 & 6, this depends on what the options are set as. See:

myob_use_addr_only, normally in MYOB you used the Country, State and Postcode fields, BUT it depends on how they have configured their MYOB. In the case of smartpractice, they are using the Addr Line 1, Line 2, Line 3 and Line 4 fields. Not Country, State and Postcode.

myob_use_name_in_addr option, use the customer name in the Line 1 field and bump Line 1 to Line 2, Line 2 to Line 3```

Line 9, that is the Order ID.

Line 11, this is the gross_price

Line 12, this is the net_price

Line 14, this is the gross_price

Line 15, this is the tax amount

Line 16, this is the delivery amount

[zvadym]

There are data what I get

sale: https://drive.google.com/file/d/0B08QiN-rbxx3S2VHaWFWT25OMzA/edit?usp=sharing customers: https://drive.google.com/file/d/0B08QiN-rbxx3WDA2ZXU1aV94Nnc/edit?usp=sharing

[r31gnf1r3]

So it needs to be exactly formatted as per the MYOB examples of a correct CSV export given for their software.. see image for DR. BENJAMIN APAP and how the formatting works.

Just from first glance:

• [x] You can't possibly know the internal Card ID and Record ID that MYOB stores,
• [x] There is no invoice numbers,
• [x] The dates aren't formatted correctly,
• [x] The addresses aren't formatted correctly,
• [x] There is no amounts for any invoices, etc. and
• [x] There is no line breaks between entries.

What happens when you change between the MYOB export different options?

[zvadym]

What happens when you change between the MYOB export different options?

check this file please https://github.com/the0forge/sp/blob/master/frontend/views/myob.py lines 62-80

There is no invoice numbers

because new migration is still not done and I have no data about the invoices

The dates aren't formatted correctly,

I use strftime("%d.%m.%Y")
In your file the format is the same

The addresses aren't formatted correctly,

check now https://drive.google.com/open?id=0B08QiN-rbxx3cnVmSC0xM01sT0E&authuser=0 https://drive.google.com/open?id=0B08QiN-rbxx3R3RuVVF6dGczREE&authuser=0

BTW this is data which I have for DR. BENJAMIN APAP