Add a field of different table

This is a tool for you to use to help add a Foreign Key View (FKV) / Sub Table View (STV) to a UI form, in order to enable a field to be added that would normally not be available.  Even though it is specific to particular tables, you are responsible for “translating it” to any other tables.

1.  Open Epicor 9 and select Options > Developer Mode.

2.  Material Management > Inventory Management > General Operations > Issue Material.

3.  In the Select Customization window, check either “Base Only” or chose a customization already created.  Then select OK.  The Issue Materials UI opens.

4.  In the Issue Material Form, select Tools > Customization.

5.  In the Customization Tools Dialog window select Tools > Data Tools.

Creating a Foreign Key View to bring in the Part so you can get to the Part Warehouse

Epicor 9 uses “Views” not hard reads on tables as you would in a programming language.  In our case the Parent view will be IM because that is what the Developers setup for the Issue Materials main view.  All views work through Adapters.  So in our example the Part Adapter is used for all things related to parts.  The view can have many other tables in it, such as the Part and the Job views.

6.  Click on the “New Custom View” button.

7.  Enter View Name.  Notice that the views do not include “Part”.  They have “IM” for “Issue Material”.  The View Name could be anything you wish to use but you want it to be unique and meaningful so we will use IM_Part.

8.  The Parent View Name will auto fill to IM.

9.  The View Type will auto fill in Foreign Key View.

10. Set Column Name to PartNum which is the primary link to the part record.

11. The Like Column Value will auto fill with Part.PartNum.

12. The Adapter Name will auto fill with PartAdapter.

13. The Get By Type will auto fill with StringGetByID.

14. Click the "Add" button in order to add the Foreign Key View and then you will receive a confirmation message.  Click the OK button.

Next we create a Sub Table View to bring in PartWhse Table, which is one of the tables in the Part view, a sub table of the Part table.

15. Click on the "New Custom View" button.

16. Enter View Name.   This could be anything you wish to use but you want it to be unique and meaningful so we will use STV_PartWhse.

17. Set Parent View Name IM_Part (our foreign key View name).

18. Set View Type to Sub Table View, because we are going after a sub table of the Part table.

19. After you select the View Type, the screen repaints with more fields:

20. The Link Columns View will automatically be set to IM.

21. Set Sub Table Name to PartWhse, the table that we are going after.

Now setup the links for the PartWhse table based on that table’s primary index.  You can find the primary index by going into the Data Dictionary Viewer.  In our case we want to add Company, PartNum, WarehouseCode.  This will allow us to only see the correct company and part number records in the PartWhse table.  We can’t add the WarehouseCode because there is no corresponding value to that in the Part table.

22. Set Parent View Columns (from the Part table) to Company.

23. Set Child View Columns (from the PartWhse table) to Company.

24. Click the "Add" button just below the Child View Columns, NOT the one at the bottom..  This adds the first link for the two tables.

25. Set Parent View Columns (from the Part table) to PartNum.

26. Set Child View Columns (from the PartWhse table) to PartNum.

27. Click the "Add" button just below the Child View Columns, NOT the one at the bottom.  This adds the second link for the two tables.

28. Click the "Add" button the bottom.  This adds the Sub Table View.

29. Click the OK button.

30. Save and exit the customization.

31. Exit the Issue Material form.

32. Re-open Issue Material form and select your customization. 

33. Add the new UD controls to the form.  You will now find that you can bind your new field to STV_PartWhse.OnHandQty.

34. Now after to bring in the “Job” and the “Mtl”, it will also fill in the quantity on hand in your new field.