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.
