Consulting

Page 4 of 6 FirstFirst ... 2 3 4 5 6 LastLast
Results 61 to 80 of 117

Thread: Convert Many Invoice Formats to a Standard Format

  1. #61
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Flexibake is not only a software company specializing in Bakeries, it is also a CodeName for in house distribution agents and a code name for in house Accounts Receivable Reports, Why not name the inhouse bits "InHouse" so you don't have to keep mansplainin' it to every noob? (|;< )

    A quick review of Flexibake's website indicates that they may not be offering any handheld hardware devices at this time. It's been 3 1/2 decades since I did any Retail B2B Distributing, but even in the '80s the Handheld devices we used had a builtin Modem to talk to a computer at the head office, Most such devices today are either wireless or Cloud capable, I don't understand why you are limiting your company to paper reports,

    Using Transaction Pro as an intermediary (along with Access 10) to import xlsx files into Quickbooks Enterprise may be a helluvalot slower than using Quickbooks native IFF import functionality.

    See: https://quickbooks.intuit.com/learn-...ders/00/201577
    And download: https://support.quickbooks.intuit.co...import_kit.zip

    Can I safely assume that the FlexItem price list is a complete list of all Corporate product names and Sell prices.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  2. #62
    SamT

    We are only using Flexibake for the ability to create our bakesheet "Is the production schedule" and DSD mobile app. When I say handhelds I am refereeing to cell phones and tablets. These are what they will be using to run the fleixbake app. They will then be printing to a zebra mobile printer.
    We are eliminating all paper reports. I am not sure what reports you refer to when you say paper reports. I might have not explained a part of the process correctly.
    The only paper will be the print out from the zebra printer of the invoice and credit memo details. The stores all require that.

    The accounting ability of flexibake is very limited and does not offer many things we need.

    I have used Quickbooks for many years. The IIF might be faster but the problem with IIF is it does not offer the amount of detail transaction pro does. There are many of fields and settings we need that IIF files just do not allow for. IIF just dont have the ability to import the data as I need. Also importing IIF is very touchy. I have had it cause database corruption more than once. I have worked with the Quickbooks support for many hours. One time I had to manually rebuild a company file. After days of working with Quickbooks support they said that was the only option to get things back to normal. None of their tools to fix the problem worked. I had to export everything then use tools to import it back into a new company file "database". This was 2 weeks worth of work. It took so long because the amount of data. It would take 5 hours just to export a months worth of invoices. I rebuilt it to include the past two years of data. That is what the management wanted.

    The FlexItem list I provided was missing an item. I attached a updated list that does include everything.
    We do add and change items at times but as of right now this is a complete list.
    Attached Files Attached Files
    Last edited by joeny0706; 09-28-2020 at 07:23 PM.

  3. #63
    Quote Originally Posted by SamT View Post
    I renamed all the modules in the 6 importing books I'm using, Then I prefixed the Sub calls so I would know which module each was in. Note: None of this effected the actual code in any way.

    Attachment 27232

    As I previous said I am new to VBA. I did not see how to rename them. When I right click it is not an option. I never did google how to rename them but that will be helpful with detailed names. I know at times I am looking at them and not having a name does make it a pain to find the one I wanted.

    I just looked it up and very simple. Just press F4

    Thanks that will be helpful.

  4. #64
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    In my previous post, the "Paper Reports" I was afraid you were talking about were the Raw Data this Project of ours was dealing with. Whew. Glad I was wrong.

    Welp. We Work with what we have. Which is
    • Raw Data Files of csv and xls
    • The Project
    • Transaction Pro + Access
    • Quickbooks


    But we can ignore Quick Books for The Project. I am tempted to preconvert that xls file to a csv, Outlook can handle that before The Project ever needs it. Having nothing but csv files means one less file type to code for.

    Having a Directory tree like C:/Data/FolderName/Archive means the code only has to worry about FolderName. All other file names are just Strings only used for SaveAs purposes.

    You understand that The Project will only speed up the process of converting Raw Data to Transaction Pro inputs? I will make The Project easier to comprehend and maintain, but, that will be the only real benefit. Reducing 15 minutes to 2 minutes isn't much of a change to 3 hours 15 minutes.

    You have a Data Field Map from the Excel generated Reports to Transaction Pro, I would like to see it. It can be in the same XL Book as the next request. The Tab Name can be T-Pro.

    I need Column Import Maps for each Raw data file you use when you import them into Excel. I know that some Raw Data files don't have Header Rows so column numbers are OK. Please put them all into one XL Book and use FolderNames for Tab Names

    The T-Pro tab
    • The first Row should be the Map Field name (indicate empty columns)
    • The second Row (Required) should be the XL Report Column Header name
    • The third row (Required) should be the Data Type required by T-Pro
    • The Fourth Row(Required) should be an example of any Formatted Inputs (Ie Dates... "yyyy/mm/dd" ???)


    The Column Import Map Tabs
    • The first Row should be the Map Field name (indicate empty columns), or your best guess. This is for informational use, the code won't use it.
    • The Second Row (required) will be the Field number of the CSV file. This is what the code will use
    • The Third Row (Required) will be the XL Report Column Header name. There will be blanks in this row, because you must map Columns to used Fields.
    • The Fourth Row(Required) will be the Data Type required by T-Pro. Same as the T-Pro tab


    Once I have all that, I think I'll have enough to get started, I will be creating at least two xlsm books, named ProjectName_Master.xlsm and ProjectName_References.xlsm.

    Use your imagination and give me a good ProjectName (|;>)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #65
    SamT


    Speeding up the process has not been a goal at all. Of course, that would help. It would be nice to not have to copy and paste all the data into the xlsm file.
    The main goal was originally to get the credits fixed. We then started the process of fixing my errors and making the process more efficient and correct.
    Now after working with you one of the goals would be to make the process easier to comprehend and maintain. After that I can possible have others do the converting and importing. It is just with the way I do it now would be difficult to teach an office staff how to do it. Even to make it easier to maintain would be great for me also. I am still unsure if I would want anyone else to do it since making a small mistake during the upload process could cause very serious problems. I can worry about that in the future. Also, once I see how you process works will help me in deciding that.

    T-Pro allows me to create data-map .dat files. When I am uploading the data, I can choose the type and it populates the mapping. I will attach the 4 .dat files.
    I am working on the excel sheets for you. I am not sure if I am understanding how you want it. I will fill it out to my best and you can let me know what I need to fix

  6. #66
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Open a .dat with Notepad, copy the ext and paste it, Transposed, into Excel. Annotate that Row so I know what is what about it.

    The only info I have at the moment is what T-Pro provides with it's Helps. 37 generic Sample Maps.

    What I need to do is Map the transformation from Raw file to Quickbooks via T-Pro. Technically all I absolutely need are the Raw Data Field numbers Mapped to the nine Columns of the T-Pro input Reports that ThisProject creates. In reality, the more I know, the more comprehensible the system I create can be and the easier it will be to use and teach.

    At 3:00 AM, your Returns invoice number woke me out of a sound sleep. Returns have no correlation to sales invoices. It seems to me (but I haven't been paying too much attention to that issue,) that you are trying to force a correlation there. QB-Enterprise is very customizable. IMO, it would be best if you merely added a Unique "Returns" Prefix to FlexItem's Return short Invoice numbers and added that function to QB.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #67
    I will send you some screen shots also of T-pro. I am able to setup the mapping by choosing the columns I want to import and match up to the fields in QB wihtin T-pro. The order of the columns on the import file does not matter since I map it once I get to that step. In the screen shot below you also see load map. I have different maps. One for midstate and Flexibake. “Those only two with discounts. I then have another map for the other two. “Ahold,Albany”.
    I can have the map and if others do the import all they will have to do is load the map and it will configure all the settings for them. If it is setup correctly so the Column header names match on all the import files I could use the same Map for all of them. If the same xlsm file is used to convert all the raw data only one saved map will be needed.
    In the zip folder it has the .dat files and also xls files.
    Also the word file has the screen shots within it.


    Yes Return identification numbers Flexibake creates have no correlation to sales invoices. That is the original reason I started this post. To create the correlation and a number that matches what is on the DSD print out.
    At first there was no credit memo number on the print out that is handed to the customers. They all require one.

    So since this needs to be on the print out it needs to be created before it gets to QB. Flexibake said the credit number is not created until the credit is within Flexibake. They only thing I was able to get Flexibake to do was put the invoice number on the credit print out.
    We did have a prefix to the invoice number. The first thing the print out had was CM-12456789
    CM-“Invoice Number” was an issue the for the drivers when they got to the stores some of them would not allow letters. It had to be all numbers. So, if it is the invoice number with a 9 in front of it is easy for Flexibake to add to credit memo print out and it will also create a correlation between invoice and credit.



    Albany Map
    [IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png[/IMG]
    Albany Map
    [IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png[/IMG]

    Midstate
    [IMG]file:///C:/Users/JoeF/AppData/Local/Temp/msohtmlclip1/01/clip_image003.png[/IMG]
    Attached Files Attached Files
    Last edited by joeny0706; 09-30-2020 at 12:41 PM.

  8. #68
    In the future their may come a time where PO is needed. I will be able to choose what column to export it to out of Flexibake. If you are able to setup a column for PO in the conversion tool that could be helpful. As long as I know what column to export the PO to if that does become a needed column that would be great.
    I am trying to get Flexibake to give the driver the ability in the DSD app to enter a PO. As of right now that can only be added from within Flexibake and that is not helpful. They said in might be possible in a future update.

  9. #69
    I have a question about adjusting one of the macros I have in my conversion xlsm files. Should I start a different post or asking in this one is fine?

    I have the macro code below look within my xlsm file and replaces text. It has a different excel file it uses to know what to replace. I am having an issue that it is replacing items it should not. I have attached the xls files it uses to know what to replace and the raw data file that it searches.

    The issue is it is searching all the columns. I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?












    Option Explicit


    Sub Replaces()
        Dim wbData As Workbook
        
        Application.ScreenUpdating = False
        
        'delete Data is if still exists
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("Data").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        'open Replaces workbook and copy data sheet in
        Workbooks.Open Filename:="C:\FlexibakeConversions\FlexreplacedataInvoice.xlsx"   '   <<<<<<<<<<<<<<<<<
        Set wbData = ActiveWorkbook
        wbData.Worksheets("Data").Copy Before:=ThisWorkbook.Worksheets(1)
        wbData.Close False
        
        ThisWorkbook.Activate
        
        'do the replaces
        Call ReplaceAllSheets(Worksheets("Data").Range("A1"))
        Call ReplaceAllSheets(Worksheets("Data").Range("D1"))
        Call ReplaceAllSheets(Worksheets("Data").Range("G1"))
        'get rid of Data
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("Data").Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        
        Application.ScreenUpdating = True
    End Sub


    'this sub is Private so that it's only usable in this module
    Private Sub ReplaceAllSheets(R As Range)
        Dim i As Long
        Dim ws As Worksheet
        Dim r1 As Range
        
        Set r1 = R.CurrentRegion
        
        If r1.Rows.Count < 2 Then Exit Sub
        
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name = "Data" Then GoTo GetNextSheet
            If ws.UsedRange.Cells.Count < 2 Then GoTo GetNextSheet
            
            For i = 2 To r1.Rows.Count
                ws.UsedRange.Cells.Replace What:=r1.Cells(i, 1).Value, Replacement:=r1.Cells(i, 2).Value, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                    SearchFormat:=False, ReplaceFormat:=False
            Next i
    GetNextSheet:
        Next
    End Sub
    Attached Files Attached Files
    Last edited by SamT; 09-30-2020 at 02:39 PM.

  10. #70
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    The order of the columns on the import file does not matter since I map it once I get to that step.
    LOL To quote you: Your system is a "mess."

    Your current system might not, but this comprehensive and logical system I'm trying to create does. If you don't want to give me what I think I need, just let me know and I will send you a Tarball of the 93 MB of stuff that I already have.

    I think I need Maps for every distributor. Preferably from Raw data to T-Pro Import Report to QB. At least from RawData to T-Pro Import memo

    In the future their may come a time where PO is needed.
    Both Credit and Invoice T-Pro reports/Memos already have a PO Header. When ThisProject is done, it will be trivial to add that to the RawData Import function. Actually, that functionality will be inherent to the way ThisProject_Reference.xlsm is parsed by ThisProject_Master.xlsm

    BTW, Have you imagineered a name for ThisProject?

    _________________________________________________________

    On a new note: While developing a master Store Replacement list I noticed that different Distributor/Clients replace the same store names with different Client specific Store names. When I started, I thought that a replacement name like 1Midstate:10155001 Mohawk Valley CC could be parsed as CorporationCode:Store. But after manipulating, filtering, and sorting the completed list I also saw 4Independent Stores:10155001 Mohawk Valley CC. Same Store, different Replacement. Is it possible for these to be standardized? If not let me know, it will add a bit of difficulty maintaining ThisProject_Reference.xlsm. Whichever way you decide, I can make it usable by ThisProject_Master.xlsm.

    As a cross check, I show 852 different stores, including the Double Entries.


    _________________________________________________________________

    The way ThisProject will work is that everything references the name of the DataStore folder in the current Directory Tree C:\Data\DataStore\Archive. This Naming Pattern is what will keep the code and Reference information so simple.

    When a DataStore is looked at, The Master will read a set of Data Containers from the Reference. As The RawData is processed all modifications to the data will be made in accordance with the Reference. Finally, the total data from all stores will be presented as an Invoice or a Return xls(x) for input to T-Pro

    At this time, I know that I will be adding a folder (Test or Testing) to the Directory tree, I will need it for testing my code and you can use it for testing any edits or additions to ThisProject_Reference.xlsm.

    I have not yet decided how to handle Errors and Omissions effecting the parsing of RawData files.
    Last edited by SamT; 09-30-2020 at 02:38 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #71
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I have a question about adjusting one of the macros I have in my conversion xlsm files. Should I start a different post or asking in this one is fine?
    This one is fine, Any Procedure you are using is too specific to be of a whole lot of general interest,

    HEIDELBERG-INVCR092920-9-26-out.csv (49.7 KB, 0 views)

    Joeny, PLEASE prefix all attachments with the DataStore Name they belong to.

    Example: Flex-HEIDELBERG-INVCR092920-9-26-out.csv
    You have uploaded three Heidelberg CSVs and this one is the only one I can assign to a DataStore folder.


    1. HEIDELBERG-INVCR091820
    2. Heidelberg KKB 083020-090520
    3. Flex HEIDELBERG-INVCR092920-9-26-out (This latest one that I renamed)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  12. #72
    I apologize I was not clear on my reasoning when I said “The order of the columns on the import file does not matter since I map it once I get to that step.” I was just trying to explain you can put the columns in the order you would like and a map can be made to have it upload correctly. I was no aware if you might have though there was a static order the data columns needed to be in.
    I am not sure how your tool works but I do think you would need a different map to go from the raw data to the conversion tool for each. Since they are all in a different order.

    I am not sure on a good name yet. Do you have any suggestion? Or maybe once I see how it works that could help. I will work on that.

    The first Midstate replace file I sent you was not correct. Once I found out I did adjust it and posted it again. Within our accounting program we have customers the under the customer there is what they call jobs. You can have many jobs under one customer. With all independent stores they are under a customer 4Independant Stores. For midstate we took all the independent stores and put them under the customer 1Midstate. So, where you see a duplicate store name the one with Independent if front needs to be removed. Where you see “4Independent Stores:10155001 Mohawk Valley CC” was changed to “1Midstate:10155001 Mohawk Valley CC” I must have missed that one when I sent the updated midstae replace file. If you remove “4Independent Stores:10155001 Mohawk Valley CC” that should remove any duplicate. The same if there are any other duplicate names just remove the one with 4Independant in front of the store name. Or if you would like me to help you can send me the list you have and I will make sure the duplicate names are removed and send it back to you.

    Again, I do want to let you know I appreciate all the work and effort you are putting into this project. There are still good people within the world. The number has dropped significantly but they are still here

  13. #73
    1. Flex HEIDELBERG-INVCR091820
    2. KKB Heidelberg KKB 083020-090520
    3. Flex HEIDELBERG-INVCR092920-9-26-out (This latest one that I renamed)



    In my current xlsm file I have having an issue with the replace macro. A product from flexibake raw data has appeared for the first time with the item code of "80". The replace macro is replacing everywhere it finds an 80. If there is a 80 in the invoice number it is replacing the 80. If the invoice is 1248043 it is changing it to 124Itemcode43. So if I was able to adjust that macro to only search the item column for the item replace data that would not happen. As I stated in that post.

    I am needing to use this tool while you create the new one. I was hoping this is an easy adjustment to the current code.
    --------------------------------------------------------------------------------------------------------------
    I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?
    ---------------------------------------------------------------------------------------------------------------


    I also attached the .bas file in the zipped folder






    Option Explicit


    Sub Replaces()
    Dim wbData As Workbook

    Application.ScreenUpdating = False

    'delete Data is if still exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    'open Replaces workbook and copy data sheet in
    Workbooks.Open Filename:="C:\FlexibakeConversions\FlexreplacedataInvoice.xlsx" ' <<<<<<<<<<<<<<<<<
    Set wbData = ActiveWorkbook
    wbData.Worksheets("Data").Copy Before:=ThisWorkbook.Worksheets(1)
    wbData.Close False

    ThisWorkbook.Activate

    'do the replaces
    Call ReplaceAllSheets(Worksheets("Data").Range("A1"))
    Call ReplaceAllSheets(Worksheets("Data").Range("D1"))
    Call ReplaceAllSheets(Worksheets("Data").Range("G1"))
    'get rid of Data
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Application.ScreenUpdating = True
    End Sub



    'this sub is Private so that it's only usable in this module
    Private Sub ReplaceAllSheets(R As Range)
    Dim i As Long
    Dim ws As Worksheet
    Dim r1 As Range

    Set r1 = R.CurrentRegion

    If r1.Rows.Count < 2 Then Exit Sub

    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Data" Then GoTo GetNextSheet
    If ws.UsedRange.Cells.Count < 2 Then GoTo GetNextSheet

    For i = 2 To r1.Rows.Count
    ws.UsedRange.Cells.Replace What:=r1.Cells(i, 1).Value, Replacement:=r1.Cells(i, 2).Value, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Next i
    GetNextSheet:
    Next
    End Sub
    Attached Files Attached Files

  14. #74
    Paul


    Is it possible to make the macro execute on the csv file I have open. I saved it to the personal workbook. So when I have the CSV open I want to convert I can just run you macro. When I do it now the first thing it does is ask what csv file I want to convert.
    Can it just execute on the csv I have open when I run the macro rather than ask me to open the file?

    Also if rather than saving the converted file it just makes the changes and I can save the file where and how I want


    The macro does work great. If that is alot of changes dont worry it is good as is.


    Thanks Much

  15. #75
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    The way I envision the ThisProject system in use is for three classes of persons, Common Users, Power Users, and VBA Coders.


    1. A Common User would open ThisProject_Master and click a Button or Shape Captioned "Create T-Pro Input Memos"
    2. A Power User, (That is you and anybody you trained,) Would maintain the data in ThisProject_Reference, which data would be laid out in a fairly self explanatory manner with hints and tips abound, for each individual Distributor (Client).
    3. A VBA Coder would be able to easily comprehend the logic and program flow of the VBA in both Master and Reference.



    _____________________________________________________________________

    you can put the columns in the order you would like and a map can be made to have it upload correctly. I was no aware if you might have though there was a static order the data columns needed to be in.
    I am not sure how your tool works but I do think you would need a different map to go from the raw data to the conversion tool for each.
    That is an issue for a PowerUser, the question is when and how. Remember the edits must be tested before the system is used for producing an actual T-Pro InputMemo.

    There are only two instances when the Reference book needs editing, for changes in Replacements, (which see above,) is pretty straight forward, and when a new Distributor (Client) is added.

    Adding a Client is done in the Reference book by:
    1. copying the existing sheet "Template_Client"
    2. Renaming the copy to suit,
    3. changing the Copies' CodeName to "Test," (F4 again.)
    4. Then filling out the information in Copies' tables exactly the same as in any other Distributor's Sheet.


    Lastly,
    1. edit the Options Sheet's "Uses Test" Cell to read True vice the standard False.
    2. Finally, click the Button or Shape Captioned "Create T-Pro Input Memos"
    3. and review the results in the folder C:\Data\Test.


    When satisfied with the Results,
    1. Add a new DataStore Folder, named to suit,
    2. Change the CodeName of the Reference Copied sheet to suit
    3. Add the new Client's Sheet CodeName to the Options Sheets "Distributors" List
    4. and resetting "Uses Test" to False.


    Note that the Name of the DataStore folder and the Codename of each Clients information sheet must be identical and have no spaces and cannot begin with a number. The sheet tab name is not used in any code.
    _________________________________________________________________

    The code in the Masterbook has three main sections
    1. Read RawData files, following details in the Reference book sheets
    2. Manipulate and edit the ReadIn data, again following details in the Reference book sheets
    3. Collate the data and create T-Pro Input memos. Once again following details in the Reference book sheets
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  16. #76
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I would like to request some changes to the T-Pro InputMemo(s)

    The purpose of of these suggestions is to make that part of the process more transparent to SuperUsers so They have that much less to learn and remember. I have tried to make these suggestions very reflective of what I Imagine the QB is using so that the Memo Headers are intuitive to SUs.

    Another purpose is to make the T-Pro Map the same for all Distributors, since they will all be input using the same T-Pro Input Memo(s). This would mean that the only individual unique Maps are from Raw Data to Input Memos. I don't have a clue as to what is going on with T-Pro and QB, that knowledge is still strictly in your domain.

    I don't know how T-Pro and QB handles empty or partially empty fields. If there is a code that tells QB to ignore this, that code can easily be inserted into empty fields.

    I am aware that these recommendations will have very little negative effect on the code of ThisProject and will even be of some benefit to any future VBA Coders.

    ________________________________________________________

    It is a prime tenet of Good Programming Practices that Names are consistent from beginning to end. The real beginning of ThisProject is the Raw Data from Distributors, which, unfortunately, you have no control over at this time. The real End of ThisProject is QB, which you have in hand. Unfortunately, T-Pro is in the muddle with it's very own Field names, which no one can control.

    Fortunately for us, You are creating Maps between Distributors and Memos, and, Memos and T-Pro, and, T-Pro and QB


    As an example of using consistent names, you have provided a list of Stores to ignore, Consistent names would have you providing a list of Customer+Jobs to ignore. Minor, I know, but it's the only real example I can come up with off the top of my head.

    All Names in ThisProject should have their genesis in your QB Charts of Accounts. That is so important that you should have the relevant COAs on your desk for easy reference

    _____________________________________________________

    At this time, you are using three flags to tell QB a record is a Return Item: An Asterisk in a Product Code, a Memo type (Return Memo) and a Template Field in the Memo. The Asterisk is almost impossible to code for since its usage does not follow a pattern; It is not always the third character; It does not always precede a space; It does not always follow numerical characters. An Asterisk has dedicated meanings, purposes, and functionality in many, including VBA, Programs. It is so critical in VBA that one can not use it as a character, one MUST use a special character code instead.
    Attached Files Attached Files
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #77
    That sounds good.


    I looked at the memo headers and looks good. I did want to make sure you knew that columns 4 and 5 need to be combined into one column with : between the customer name and the job. The same need to be with the product code and product description. They need to be combined with : between the two.

    Would it be helpful if all the return items are two digits followed by a *. We only accept returns for the items below. I would only need to change the top 3 if it would be helpful to have two digits * "23*,24*"




    Q000212 2Pk*Cracked Wheat
    Q000262 2Pk*French Peasan
    Q000452 2Pk*Multigrain
    Q00021 21* CW
    Q00023 23* HR
    Q00026 26* FP
    Q00022 22* RYE
    Q00024 24* PUMP
    Q00025 25* WW
    Q00033 33* Biaggio Asti Italian
    Q00028 28* WB
    Q00027 27* SD
    Q00031 31* OAT
    Q00034 34* JR
    Q00045 45* Multi Grain
    Q00068 68* Hamburg Buns
    Q00069 69* Hot Dog Buns
    Q00621 21* CW
    Q00622 22* RYE
    Q00626 26* FP
    Q00645 45* Multi Grain
    Q00030 30* RS
    Q00029 29* DR
    Q00050 50* Wheated 12 Grain
    Q00040 40* Flaxseed
    Q00070 70* Heidelberg Italian

  18. #78
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Joeny, I need your explicit approval of Memo Headers v2 for approval.xlsx. Look it over carefully before you say yes. This is what I intend to code for.
    SamT. 10.02.2020
    Attached Files Attached Files
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  19. #79
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @SamT

    You have mine

  20. #80
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •