Multi-line batch purchase invoices

1

Good afternoon again.
Creating a batch purchase invoice, all the references and testing I can do has all the line items on the same row of data. So, the basic information and then Line1.item, Line1.qty, Line1.unit cost, followed by Line2, Line3, etc. All on the same row. Is there a method to create these so that you have rows of data for the same invoice instead of having on the same row? If you have an purchase invoice with 50 items, having to format and manually create Lines for a purchase invoice, kind of defeats the purpose of the batch since it saves no time due to all the formatting necessary. Is there an easier way? Thanks.

2

Hi @JackieBlue,

Unfortunately, this is the format we are stuck with since it’s definitely easier for starter but as you described a major obstacle to slightly more advanced users.

It’s still possible though to use your spreadsheet coding tool to stage the data back and forth from this format to any other format.

It’s not easy though

3

Thanks for your response. I will continue to test using manual entry for now (which we do for QB currently) and worry about automation/speeding work flows later. As you noted, I can write macros to do a lot of the work involved with that.

4

What are you actually trying to achieve?

  1. Do you have many invoices in another system you want to convert to Manager all at the same time?
  2. Do you have another system which you used to generate invoices and you want to regularly import them in to manager maybe a few at a time or just individually
  3. Are you generating invoices in a spreadsheet (or other program) you have written which you want to import into Manager as they are created?

It’s hard to offer sensible information without understanding what you want to do.

5

Thanks for your response. I will try to rephrase. You have an invoice in a spreadsheet. The lines have 1 sku per row. So it will look like SKU, QTY, PRICE. Say there are 50 items. 50 rows of items on one invoice. Manager, instead of having a multi-row invoice in the batch update, prefers 1 row per invoice. It is not grouped by reference as far as I can tell. So in this example, you have to put all 50 items on one row. Lines.1.Item Lines.1.Qty Lines.1.PurchaseUnitPricem Line.2.Item.qty, Line.2.PurhaseUnitPricem , etc. Ealfardan replied this wasn’t possible. At some point I will just write a macro to convert and create these lines if it is not possible to group rows to one invoice on the batch create. It is not a huge deal - we do it manually in QB currently.

6

The question was not what data structure Manager uses in Batch operations, most people who have used batch operations extensively already know that. I was interested in what business work flow you are trying to accommodate.

7

My question was confirming the data structure. I wanted confirmation that there wasn’t something I was missing in that structure. When starting out I will have open invoices to enter. Going forward I want to automate that process more instead of paying a data entry freelancer to do it. Assuming I switch to Manager from QB, I will address it later with automation. What I was testing was taking PDF purchase invoices, converting them to spreadsheets, and then batch create . It was just an automation test. Most of my tests are merely recreating our current process and making notes to update future procedures for how Manager does things differently. But, since I was testing some other batch create yesterday I thought I would give it a shot. I now know what the parameters are. Thank you.

8

I can see value in achieving that. Buy the time you have extracted data from a variety of invoices, identified which fields the data goes in and removed junk, putting the data in Managers batch import format should be trivial.

However if that’s you plan, could I suggest you look at Manager’s API, that would enable your external data parsing program to automate data transfer to Manager. Search results for 'api2 order:latest' - Manager Forum

1 Like
9

Thanks. That would be ideal. Didn’t realize they had an API since I was concentrating on reproducing my current process. As a former programmer, I should be able to make that happen after we convert over.

1 Like
10

If you don’t use an API, you can do the conversion in Excel PowerQuery.

  1. Create multiple row table called Table1
  2. Make separate the headers that don’t contain Lines.1.* from those those that do.
    table
    table868Ă—181 7.18 KB
  3. Load Table1 into PQ, then copy the following:
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {       
        "Reference",         
        }, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
    #"get index for xform" = Table.Group(#"Removed Columns", {  
        "Reference",         
        "Attribute.1", 
        "Attribute.3"}, 
        {{"All", each _, type table [           
            Reference=nullable text,            
            Attribute.1=nullable text, 
            Attribute.3=nullable text, 
            Value=text]
        }}),
    #"Added Custom" = Table.AddColumn(#"get index for xform", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"show index" = Table.Combine(#"Removed Other Columns"[Custom]), 
    #"Sorted Rows" = Table.Sort(#"show index",{{"Index", Order.Ascending}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Index", type text}}, "en-US"),{"Attribute.1", "Index", "Attribute.3"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

You can transform a lot of rows at once, but keep in mind since Excel is limited to 16384 columns you can’t do more than that in rows. In practice you probably can’t do more than 2500 rows at once.

1 Like
11

Thank you. This might be good to do some stuff for conversion. Longer term I will probably just write a program that uses the API as well as some business checking to simply it further.