Importing Inventory and BOM information from text file

[kbrault]

Hello everyone,

I am new to MAS 90 and am looking for a little help.

Here is the process at our company:

1. Engineering assigns a part number in MAS 90 (all the fields are left blank).
2. Engineering designs the product
3. At the click of a button engineering exports a BOM and data files from the CAD system.

1. The BOM file contains "Part number", 'Revision", "Quantity"
2. The data file contains "Part Number", "Revision", "Description", "Manufacturer", "Manufacturer's Part Number"
3. The files form the CAD system are very configurable (tab delimited, Comma delimited, etc)

4. Document Control manually enters the data form the CAD files into MAS 90

I would think we could import the data from these files into MAS 90 somehow. Reducing manpower and errors.

I would greatly appreciate and help with this.


Thank you in advance for your assistance.


Kevin

 

[Blorf]

Do you have the Visual Integrator module? If not, you will need it, or one of the other bolt on modules.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[kbrault]

Thanks ChaZ,

When I log into MAS 90 in the left Window I see these items under Modules:

Library Master
Business Insights
General Ledger
Accounts Receivable
Accounts Payable
Payroll
Inventory Management
Sales Order
Purchase Order
Bill of materials
Work Order
Bank Reconcilliation
Report Master
FAS Link
Custom Office


Is this what you are asking about? If not were do look to find the modules we need?


Thanks again


Kevin

 

[Blorf]

Hi. If you had visual Integrator, you would have it listed in that list. Further, I don't see any other varieties of modules designed to allow for this.

OK, knowing that, first thing to think of is that Mas 90 version 3.X stores it's data in a dos style file format, native only to Mas, so there is no real way to do this with out paying out the cash to buy the modules you need.

I use Visual Integrator, not because it's the best, but because that's what we have, so I can't speak about any of the others, since I have never used them. I would guess the module would cost you about 2 thousand. Next, sadly, is learning how to use it, since it is strange and off putting.

What's neat I guess is that I wrote jobs in this module to do nearly the same thing you are talking about, except I had to write code in Solid Works to export the data first, then lanuch the Mas job to import the data, so I could probably help, but as it is, you have not import module to work with.

I would use google or what not, or even log on to best, and look for modules to import data, and see which one works best for your company, then spend the cash, since after about 2 months of use, the data entry time going away will pay for the module.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[kbrault]

Hi ChaZ,

Well I just sent off a P.O. for Visual Integrator. We are going ahead with this project.

Thanks again


Kevin

 

[kbrault]

Hi Chaz,

You know I thought I had seen a post from you offering to post your code on Monday.

I do not see it now. Do you know what happened? I have not seen your code yet!


Thanks


Kevin

 

[Blorf]

You are indeed correct.

Sorry. The job is below. You mentioned you are purchasing VI, so I am guessing the job will be new stuff to you. I will be happy to answer questions.

ChaZ

Run Date: 12/20/2005 CRS D/T CARSON ENTERPRISES, INC. Page: 1
V/I Date: 12/20/2005 JOB DEFINITION PRINTING FOR JOB: SW_BOM Time: 10:52 AM
Importing Items into Item Master Complete

Will Import BOM's Now.

CONFIGURATION INFORMATION
-------------------------------------------------------------------------------------------------------------
FILE NAME: BM.BILL Bill of Materials - W/Opts
DATA SOURCE: SolidWorks Access Project
FILE TYPE: ODBC
INSERT ALL FIELDS DURING SETUP: Y
HEADER INFORMATION INCLUDED IN DETAIL LINES: N
PASSWORD: NO

DATA ITEMS
-------------------------------------------------------------------------------------------------------------
ITEM ITEM NAME FG TYPE OPERATION COLUMN IMPORT MASK SUB-STRING
-------------------------------------------------------------------------------------------------------------
00001 BILL_NUMBER H STRING REPLACE 00001 Start: 1 Len: 20 No
00002 BILL_REVISION H ACCOUNT REPLACE 00002 ### No
00003 MAIN_BILL_BLNKS H1 STRING REPLACE 00003 Start: 1 Len: 3 No
00004 OPTION_CATEGORY H2 STRING REPLACE 00004 Start: 1 Len: 1 No
00005 OPTION_CODE H2 STRING REPLACE 00005 Start: 1 Len: 2 No
00006 DESCRIP_1 H STRING REPLACE 00006 Start: 1 Len: 30 No
00007 DESCRIP_2 H STRING REPLACE 00007 Start: 1 Len: 30 No
00008 BILL_TYPE H STRING REPLACE 00008 Start: 1 Len: 1 No
00009 DRAW_NUM H STRING REPLACE 00009 Start: 1 Len: 20 No
00010 DRAW_REV H STRING REPLACE 00010 Start: 1 Len: 3 No
00011 LAST_USED H DATE REPLACE 00011 YYYY/MM/DD No
00012 ROUTING_NUM H STRING REPLACE 00012 Start: 1 Len: 20 No
00013 HAVE_OPT H1 YES/NO REPLACE 00013 Start: 1 Len: 1 No
00014 CURRENT_REV H ACCOUNT REPLACE 00014 ### No
00015 OPT_INTERACT H1 YES/NO REPLACE 00015 Start: 1 Len: 1 No
00016 OPT_CATS_DEF H1 STRING REPLACE 00016 Start: 1 Len: 1 No
00017 PRNT_SO_COMP H1 YES/NO REPLACE 00017 Start: 1 Len: 1 No
00018 STEP_NUMBER H2 ZERO-FILL REPLACE 00018 Start: 1 Len: 4 No
00019 MAX_LOT_SIZ H NUMBER REPLACE 00019 No
00020 YIELD_PERCNT H NUMBER REPLACE 00020 No
00021 OPTION_PRICE H NUMBER REPLACE 00021 No
00022 ITEM_CODE L3 STRING REPLACE 00022 Start: 1 Len: 15 No
00023 LINE_TYPE L STRING REPLACE 00023 Start: 1 Len: 1 No
00024 COMP_REV_CODE L3 ACCOUNT REPLACE 00024 ### No
00025 FIND_NUM L3 STRING REPLACE 00025 Start: 1 Len: 5 No
00026 ADD_ECO_NUM L3 STRING REPLACE 00026 Start: 1 Len: 6 No
00027 ADD_ECO_DATE L3 DATE REPLACE 00027 YYYY/MM/DD No
00028 DEL_ECO_NUM L3 STRING REPLACE 00028 Start: 1 Len: 6 No
00029 DEL_ECO_DATE L3 DATE REPLACE 00029 YYYY/MM/DD No
00030 WO_STEP L3 STRING REPLACE 00030 Start: 1 Len: 4 No
00031 BILL_TYPE L3 STRING REPLACE 00031 Start: 1 Len: 1 No
00032 QTY_PER_BILL L6 NUMBER REPLACE 00032 No
00033 SCRAP_PERCENT L3 NUMBER REPLACE 00033 No
00034 COMMENT L4 STRING REPLACE 00034 Start: 1 Len: 50 No
00035 MISC_SLASH L7 STRING REPLACE 00035 Start: 1 Len: 1 No
00036 MISC_CODE L7 STRING REPLACE 00036 Start: 1 Len: 6 No
00037 MISC_DESC L5 STRING REPLACE 00037 Start: 1 Len: 30 No
00038 MISC_GL_ACCNT L5 ACCOUNT REPLACE 00038 ######### No
00039 POST_BY_WHSE L5 YES/NO REPLACE 00039 Start: 1 Len: 1 No
00040 SETUP_CHARGE L5 YES/NO REPLACE 00040 Start: 1 Len: 1 No

Run Date: 12/20/2005 CRS D/T CARSON ENTERPRISES, INC. Page: 2
V/I Date: 12/20/2005 JOB DEFINITION PRINTING FOR JOB: SW_BOM Time: 10:52 AM

DATA ITEMS
-------------------------------------------------------------------------------------------------------------
ITEM ITEM NAME FG TYPE OPERATION COLUMN IMPORT MASK SUB-STRING
-------------------------------------------------------------------------------------------------------------
00041 UM L5 STRING REPLACE 00041 Start: 1 Len: 4 No
00042 STANDARD_COST L NUMBER REPLACE 00042 No
00043 LINE_INDEX L ZERO-FILL REPLACE 00043 Start: 1 Len: 6 No

RECORD SELECTION
-------------------------------------------------------------------------------------------------------------
ITEM TYPE DESCRIPTION COLUMN START LENGTH RELATION VALUE
-------------------------------------------------------------------------------------------------------------
00001 2 Option Bill 00046 00001 00001 = 0
00002 3 Component Line 00046 00001 00001 = 1

VALIDATION RECORDS
-------------------------------------------------------------------------------------------------------------
ITEM FIELD METHOD SKIP FILE/STRING/EXPRESSION
-------------------------------------------------------------------------------------------------------------
00001 BILL_NUMBER EXPRESSION N BM1$(1,20)<>DIM(20)
00002 BILL_REVISION EXPRESSION N BM1$(21,3)<>DIM(3)
00003 MAIN_BILL_BLNKS EXPRESSION N BM1$(24,3)=DIM(3)
00004 BILL_TYPE STRING N SKPEI
00005 ROUTING_NUM FILE Y WO.ROUTING
00006 HAVE_OPT UPPERCASE N YN
00007 CURRENT_REV EXPRESSION N BM1$(138,3)<>DIM(3)
00008 OPT_INTERACT UPPERCASE N YN
00009 PRNT_SO_COMP UPPERCASE N YN
00010 ITEM_CODE FILE N IM.ITEM
00011 LINE_TYPE STRING N 123
00012 COMP_REV_CODE EXPRESSION N BM2$(22,1)<>" "
00013 MISC_SLASH STRING N /
00014 MISC_CODE FILE Y BM.MISC
00015 MISC_GL_ACCNT FILE N GL.ACCOUNT
00016 POST_BY_WHSE UPPERCASE N YN
00017 SETUP_CHARGE UPPERCASE N YN

ODBC TABLES
-------------------------------------------------------------------------------------------------------------
ITEM TABLE NAME
-------------------------------------------------------------------------------------------------------------
00001 ZZZZ_BM_ImportQ

ODBC FIELDS
-------------------------------------------------------------------------------------------------------------
ITEM FIELD NAME
-------------------------------------------------------------------------------------------------------------
00001 ZZZZ_BM_ImportQ.BillNumber
00002 ZZZZ_BM_ImportQ.Revision
00003 ZZZZ_BM_ImportQ.billblanks
00004 ZZZZ_BM_ImportQ.OptionCategories
00005 ZZZZ_BM_ImportQ.OptCode
00006 ZZZZ_BM_ImportQ.BillDescription1
00007 ZZZZ_BM_ImportQ.BillDescription2
00008 ZZZZ_BM_ImportQ.BM1_BillMaterialsHeader_BillType
00009 ZZZZ_BM_ImportQ.DrawingNumber
00010 ZZZZ_BM_ImportQ.DrawingRevision

Run Date: 12/20/2005 CRS D/T CARSON ENTERPRISES, INC. Page: 3
V/I Date: 12/20/2005 JOB DEFINITION PRINTING FOR JOB: SW_BOM Time: 10:52 AM

ODBC FIELDS
-------------------------------------------------------------------------------------------------------------
ITEM FIELD NAME
-------------------------------------------------------------------------------------------------------------
00011 ZZZZ_BM_ImportQ.LastUsed
00012 ZZZZ_BM_ImportQ.RoutingNumber
00013 ZZZZ_BM_ImportQ.HaveOptions
00014 ZZZZ_BM_ImportQ.CurrentRevision
00015 ZZZZ_BM_ImportQ.OptionInteractions
00016 ZZZZ_BM_ImportQ.OptEek
00017 ZZZZ_BM_ImportQ.PrintComponentDetail
00018 ZZZZ_BM_ImportQ.Step
00019 ZZZZ_BM_ImportQ.MaxLotSize
00020 ZZZZ_BM_ImportQ.YieldPercent
00021 ZZZZ_BM_ImportQ.OptionPrice
00022 ZZZZ_BM_ImportQ.ComponentItemCode
00023 ZZZZ_BM_ImportQ.LineType
00024 ZZZZ_BM_ImportQ.ComponentRevision
00025 ZZZZ_BM_ImportQ.EngDrawingFindNumber
00026 ZZZZ_BM_ImportQ.EngChgAddNumber
00027 ZZZZ_BM_ImportQ.EngChgAddDate
00028 ZZZZ_BM_ImportQ.EngChgDeleteNumber
00029 ZZZZ_BM_ImportQ.EngChgDeleteDate
00030 ZZZZ_BM_ImportQ.WorkOrderStepNumber
00031 ZZZZ_BM_ImportQ.BM2_BillMaterialsDetail_BillType
00032 ZZZZ_BM_ImportQ.QtyPerBill
00033 ZZZZ_BM_ImportQ.ScrapPercent
00034 ZZZZ_BM_ImportQ.Comment
00035 ZZZZ_BM_ImportQ.miscSlash
00036 ZZZZ_BM_ImportQ.MiscChrgCode
00037 ZZZZ_BM_ImportQ.MiscChargeDescription
00038 ZZZZ_BM_ImportQ.MiscChargeGLAcct
00039 ZZZZ_BM_ImportQ.PostByWhse
00040 ZZZZ_BM_ImportQ.SetupCharge
00041 ZZZZ_BM_ImportQ.StandardCostPerUM
00042 ZZZZ_BM_ImportQ.sc
00043 ZZZZ_BM_ImportQ.artLineIndex_Pretext
00044 ZZZZ_BM_ImportQ.SortNumber
00045 ZZZZ_BM_ImportQ.FilterA
00046 ZZZZ_BM_ImportQ.FilterB

SQL STATEMENT
-------------------------------------------------------------------------------------------------------------
SELECT
ZZZZ_BM_ImportQ.BillNumber,
ZZZZ_BM_ImportQ.Revision,
ZZZZ_BM_ImportQ.billblanks,
ZZZZ_BM_ImportQ.OptionCategories,
ZZZZ_BM_ImportQ.OptCode,
ZZZZ_BM_ImportQ.BillDescription1,
ZZZZ_BM_ImportQ.BillDescription2,
ZZZZ_BM_ImportQ.BM1_BillMaterialsHeader_BillType,
ZZZZ_BM_ImportQ.DrawingNumber,
ZZZZ_BM_ImportQ.DrawingRevision,
ZZZZ_BM_ImportQ.LastUsed,
ZZZZ_BM_ImportQ.RoutingNumber,
ZZZZ_BM_ImportQ.HaveOptions,

Run Date: 12/20/2005 CRS D/T CARSON ENTERPRISES, INC. Page: 4
V/I Date: 12/20/2005 JOB DEFINITION PRINTING FOR JOB: SW_BOM Time: 10:52 AM

SQL STATEMENT
-------------------------------------------------------------------------------------------------------------
ZZZZ_BM_ImportQ.CurrentRevision,
ZZZZ_BM_ImportQ.OptionInteractions,
ZZZZ_BM_ImportQ.OptEek,
ZZZZ_BM_ImportQ.PrintComponentDetail,
ZZZZ_BM_ImportQ.Step,
ZZZZ_BM_ImportQ.MaxLotSize,
ZZZZ_BM_ImportQ.YieldPercent,
ZZZZ_BM_ImportQ.OptionPrice,
ZZZZ_BM_ImportQ.ComponentItemCode,
ZZZZ_BM_ImportQ.LineType,
ZZZZ_BM_ImportQ.ComponentRevision,
ZZZZ_BM_ImportQ.EngDrawingFindNumber,
ZZZZ_BM_ImportQ.EngChgAddNumber,
ZZZZ_BM_ImportQ.EngChgAddDate,
ZZZZ_BM_ImportQ.EngChgDeleteNumber,
ZZZZ_BM_ImportQ.EngChgDeleteDate,
ZZZZ_BM_ImportQ.WorkOrderStepNumber,
ZZZZ_BM_ImportQ.BM2_BillMaterialsDetail_BillType,
ZZZZ_BM_ImportQ.QtyPerBill,
ZZZZ_BM_ImportQ.ScrapPercent,
ZZZZ_BM_ImportQ.Comment,
ZZZZ_BM_ImportQ.miscSlash,
ZZZZ_BM_ImportQ.MiscChrgCode,
ZZZZ_BM_ImportQ.MiscChargeDescription,
ZZZZ_BM_ImportQ.MiscChargeGLAcct,
ZZZZ_BM_ImportQ.PostByWhse,
ZZZZ_BM_ImportQ.SetupCharge,
ZZZZ_BM_ImportQ.StandardCostPerUM,
ZZZZ_BM_ImportQ.sc,
ZZZZ_BM_ImportQ.artLineIndex_Pretext,
ZZZZ_BM_ImportQ.SortNumber,
ZZZZ_BM_ImportQ.FilterA,
ZZZZ_BM_ImportQ.FilterB
FROM
ZZZZ_BM_ImportQ




There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.