ERPPicker_RefManual en GB
ERP picker
Autodesk Inventor addin
Purpose
preview from Inventor to the database of items, filtering and sorting
transfer of the selected record attributes to the iProperties of the component in Inventor
Use
in Inventor call the window using the "CS ERP picker" command
in the window use the search criteria above the data table
mark the selected record and use its data according to its purpose
menu
or they have to mark the component as a purchased part (identification of the purchased component - e.g.
engine, hinge, ...) - then from the menu
in both cases the values of the selected row are written to the iProperties of the Inventor component according to the mapping set in the configuration
the same action is performed by double-clicking on the record (with Ctrl for the "Pick C & Exit" action)
Sheet thickness transfer to the search filter
the sheet metal part thickness can be used for filtering the data table - i.e. the Picker configuration can be set so that the data table is filtered according to the value read from the sheet metal part definition in Inventor already when running over the sheet metal part
The MJ used to express the thickness of the plate are [mm]
Default filtering
the Picker configuration can be set so that the data table is filtered by the column and value in the configuration at startup
Installation
is done with the .msi file
the target directory is: "<Public Documents>\CADStudio\RawMatPicker"
configuration is done using the .ini file in this folder
after installation, buttons will appear in Inventor:
"CS ERP picker" - calling the window with the "ERP" data table
"Config" - test of data source availability
addin logs activities to the folder "<Public documents>\CADStudio\RawMatPicker"
automatic license activation:
In bulk installations, the activation license key can be written to a file:
%LOCALAPPDATA%\CAD Studio a.s\RawMatPicker\lic.txt
Configuration
Configuration file location
The name of the configuration file is RawMatPicker.ini
this file can be placed in different folders - depending on customer requirements
The configuration must be different for different Win users within the computer "roaming" folder: c:\Users\<USER>\AppData\Roaming\CadStudio\RawMatPicker\
The configuration is the same for all users within the computer "public" folder: c:\Users\Public\Documents\CADStudio\RawMatPicker\
Uniform settings for all computers
create a registry key HKEY_CURRENT_USER\SOFTWARE\CadStudio\RawMatPicker\Settings create a string value named ConfigFullFileName in this key
Procedure for finding the configuration file:
if the file is not found in the "roaming" folder or the registry folder, the "public" folder is used
Contents of the configuration file
The .ini file is divided into sections that contain individual keys
section [Settings]
ConnectionString: connection to the database resource
ConnectionSelectCommand: basic definition of columns, links between tables, conditions and sorting (standard SQL syntax)
ConnectionSelectHeaders: (optional) list of names for column headers of the data table
MainFormLabel: (optional) title for the header of the whole dialog (e.g. SAP Lookup) and also the name of the button in Inventor;
(the change in .ini will be reflected in INV after reboot)
MainFormIcon: (optional) icon for the header of the whole window (name of the .ico file) and also the button icon in Inventor (raster formats are accepted);
(the change in .ini will be reflected in INV after reboot)
ShowAll: the (optional) setting True ensures that immediately after the window starts the data table is filled with all records that match the query described by ConnectionSelectCommand;
True setting is therefore undesirable, it unnecessarily uses up the data source and the data network
ApplyFilterDelay: (optional) time in [ms], if the filter content above the data grid is not changed after this time, the filter condition is applied
FullRowSelect: (optional) selection method in the data grid; (default=True) - clicking selects the whole row; if set to False, clicking selects one cell (reason = possibility of copying values of individual cells for other purposes)
SheetMetalThicknessDbFieldName: name of the db field by which the sheet thickness value is filtered
SheetMetalThicknessUseDotAsDecDelim: (default = False) if True, then a dot is used as decimal separator in the value for filtering by sheet thickness (i.e. regardless of the setting "CurrentCulture" on the computer)
InitiateFilterDbFieldName: db field name for the initial filtering
InitiateFilterValue: value for initial filtering
section [Mappings_xxxx]
mapping to the Inventor file iProperties during the "Pick" action:
the name of the key is the name of the iProperty
the key value is the column name (according to ConnectionSelectCommand, not according to ConnectionSelectHeaders),
iProperty affiliation to the iProperty set - see attachment
section [MappingsC_xxxx]
mapping to the Inventor file iProperties during the "Pick C" action:
possibility to use a different mapping for transmission with component identification (C = Component)
if the user double-clicks on the selected dataset row, it is necessary to hold the Ctrl key to trigger this transfer
Notes
the original naming of RawMatPicker is still used for internal objects and license
Attachments
Inventor - features
Sets of properties
Description | GUID | From |
Inventor Summary Information Inventor Summary | {F29F85E0-4FF9-1068-AB91-08002B27B3D9} | ISI |
Inventor Document Summary Information Document summary information | {D5CDD502-2E9C-101B-9397-08002B2CF9AE} | DSI |
Design Tracking Properties Features Design Tracking | {32853F0F-3444-11D1-9E93-0060B03C1CA6} | DTP |
Inventor User Defined Properties User-defined properties | {D5CDD505-2E9C-101B-9397-08002B2CF9AE} | UDP |
Fixed (system) properties
complete list of iFeatures (version Inv 2017)
From | Name | Display name | |
DTP | Appearance | Appearance | |
ISI | Author | Author | |
DTP | Authority | Answered by | |
DTP | Catalog Web Link | Web link to the catalogue | |
DTP | Categories | Category | |
DSI | Category | Category | |
ISI | Comments | Comments | |
DSI | Company | Company | |
DTP | Cost | Cost | |
DTP | Cost Centre | Cargo centre | |
DTP | Creation Time | Date of creation | |
DTP | Date Checked | Date of inspection | |
DTP | Defer Updates | Postpone updates | |
DTP | Density | Density | |
DTP | Description | Description | |
DTP | Design Status | Design status | |
DTP | Designer | Designer | |
DTP | Document SubType | Component type | |
DTP | Document SubType Name | Name of the component type | |
DTP | Engineer | Engineer | |
DTP | Engr Approved By | Techn. contr. Approved by | |
DTP | Engr Date Approved | Date of approval of technical inspection | |
DTP | External Property Revision Id | External component revision | |
DTP | Flat Pattern Area | Area of the boundaries of the development | |
DTP | Flat Pattern Defer Update | Postpone the update of the development | |
DTP | Flat Pattern Length | Length of the development limits | |
DTP | Flat Pattern Width | Width of the development limits | |
DTP | Checked By | Checked out | |
ISI | Keywords | Keywords | |
DTP | Language | Language | |
ISI | Last Saved By | Last time he saved | |
DTP | Last Updated With | Last updated with | |
DSI | Manager | Head of |
DTP | Manufacturer | Producer | |
DTP | Mass | Weight |
DTP | Material | Material | |
DTP | Material Identifier | Material identifier | |
DTP | Mfg Approved By | The production was approved by | |
DTP | Mfg Date Approved | Date of approval of production control | |
DTP | Parameterized Template | Parameterized template | |
DTP | Part Icon | Component icon | |
DTP | Part Number | Part Number | |
DTP | Part Property Revision Id | Component revision | |
DTP | Project | Project | |
DTP | Proxy Refresh Date | Restore proxy data | |
ISI | Revision Number | Revision Number | |
DTP | Sheet Metal Area | SheetMetalArea | |
DTP | Sheet Metal Length | SheetMetalLength | |
DTP | Sheet Metal Rule | The sheet rule | |
DTP | Sheet Metal Width | SheetMetalWidth | |
DTP | Size Designation | Size designation | |
DTP | Standard | Standard | |
DTP | Standard Revision | Revision of the standard | |
DTP | Standards Organisation | Organisation - Standards | |
DTP | Stock Number | Stock number | |
ISI | Subject | Subject | |
DTP | SurfaceArea | Surface area | |
DTP | Template Row | Template row | |
ISI | Thumbnail | Preview | |
ISI | Title | Title | |
DTP | User Status | User status | |
DTP | Valid MassProps | Valid MassProps value | |
DTP | Vendor | Supplier | |
DTP | Volume | Volume | |
DTP | Weld Material | Weld material |
Vault - SELECT
SELECT example for verification:
SELECT ItemNumber, ItemTitle, ItemDetail, units, CategoryName, IsConsumable, IsObsolete, CONVERT(NVARCHAR(10), (SELECT TOP (1) Value FROM dbo.Property AS P WHERE (EntityID =
dbo.vw_ItemRevision.ItemIterationID) AND (PropertyDefID=166)) AS TpvZm FROM dbo.vw_ItemRevision
WHERE (ItemIterationID IN (SELECT MAX(ItemIterationID) AS E1 FROM dbo.vw_ItemIteration AS I1 GROUP BY itemMasterID))
highlighted in yellow is the part of the SQL query that retrieves the user property
sql function CONVERT converts a value (table Property, field Value) from the sql_variant type to a specific type to
ERP picker over user property enabled filtering
the PropertyDefID value for the required property can be found in the PropertyDef table
example script to create View in db storage:
CREATE VIEW [dbo].[CS_ItemMaster] AS
SELECT ItemNumber, ItemTitle, ItemDetail, units, CategoryName, IsConsumable, IsObsolete, CONVERT(NVARCHAR(10), (SELECT TOP (1) Value FROM dbo.Property AS P WHERE
(EntityID=dbo.vw_ItemRevision.ItemIterationID) AND (PropertyDefID=166)) AS TpvZm
FROM dbo.vw_ItemRevision WHERE (ItemIterationID IN (SELECT MAX(ItemIterationID) AS E1 FROM dbo.vw_ItemIteration AS I1 GROUP BY itemMasterID))
final configuration of the ERP picker:
ConnectionString=Server=VaultSrv; Database=Vault; User ID=UserName; Password=UserPwd ConnectionSelectCommand=SELECT * FROM CS_ItemMaster
Configuration examples - Connection String and Command
MS SQL
ConnectionString=Server=mySqlSrv; Database=Helios001; User ID=HeReader; Password=HeReaderPwd ConnectionSelectCommand=SELECT id, group, regcis, name1, name2 FROM TabKmenZbozi ConnectionSelectHeaders=_|SZ|Reg. number|Name 1|Name 2
Excel
ConnectionString=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\OleDbTest.xls;Extended Properties="Excel 12.0;HDR=YES"
ConnectionSelectCommand=SELECT * FROM [List1$]
Access
ConnectionString=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vm- cadsadms\POHODA\Data\12345678_2015.mdb
ConnectionSelectCommand=SELECT IDS, Name, Name1, Name2, SText, SText1, SText2, MJ, MJ2, MJ3, EAN, ObjName, Company, Sales FROM Skz
Text, csv, ...
ConnectionString=DRIVER=Microsoft Access Text Driver (*.txt, *.csv); DefaultDir=d:\t_drive\Techbase\Development\STROS\db
ConnectionSelectCommand=SELECT * FROM `POL_NAK.txt` `POL_NAK` WHERE LEN(CMAT) > 0 ORDER BY 2
Vault Database
Vault queries are more complex, so it is advisable to create a view in the db storage first and then use it in ERP picker - see attachment
Potential for addin development
Support for transfer of metallurgical material quantities to ERP
try to apply the MJ-based logic that the selected row has in ERP
if metallurgical material enters the manufactured part,
if sheet metal, then limits of development OR property values? parameters (default names)
if the G_L parameter exists
Filtering by sheet metal
in addition to the thickness, you can apply another filter - e.g. "Sheet*"