Purchase Order value list based upon vendor

[rmanh]

We have multiple vendors and each vendor has parts / materials / components we purchase from them. Issue is the value list for our Purchase Order forms is now has so too many entries, too the point its not useful. Is their a way to create a script that pulls up a different set of value list based upon the vendor selected? Or is their a different way of handling this for creating purchase orders?

[siroos12]

Hi rmanh,

 

No script needed, You can create Table Occurrences based on the parts table and relate them to the Order table using vendor ID or name.

So to elaborate;

 

1- You will have a table called "Orders" which keeps record of your purchase orders including Vendor ID or Vendor Name.

2- You will need another table called parts which keeps record of the parts and their vendors. (rather use a unique ID for each vendor).

3- Now make a table occurrence based on the "Parts" table and call it something that make sense to you (like "Parts to filter by Vendors") and relate the this table to "Orders" table using the Vendor ID or Vendor name. 

4- You are half way through, at this stage, make a value list based on the parts name from part table. You would need to check the radio button says "include only related values starting from" and then choose the table occurrence you added for the Vendors. 

 

All done!

 

Regards,

 

Siroos

 

[rmanh]

Thanks Siroos, I'm thinking now I will need to take this to a new different level of table occurrences and relationships. Maybe instead of creating value lists, create a vendor table that lists for each vendor, parts ID, parts description, unit and price plus inventory tables for each part. Its starting to get complicated for a fm beginner. Be nice if their was already a template for this kind of solution? 

[comment]

5 minutes ago, rmanh said:

 Maybe instead of creating value lists, create a vendor table that lists for each vendor, parts ID, parts description, unit and price plus inventory tables for each part.

You're making this more complicated that it needs to be. You only need to select the vendor for the purchase order, then use a value list that show only that vendor's products - IOW, a value list that shows only related products from a relationship defined as:

PurchaseOrders::VendorID = Products 2::VendorID

where Products 2 is a new occurrence of the Products table.

[siroos12]

43 minutes ago, rmanh said:

Thanks Siroos, I'm thinking now I will need to take this to a new different level of table occurrences and relationships. Maybe instead of creating value lists, create a vendor table that lists for each vendor, parts ID, parts description, unit and price plus inventory tables for each part. Its starting to get complicated for a fm beginner. Be nice if their was already a template for this kind of solution? 

I made a quick sample for you. Please note that this is just a very simple sample, it need a lot of more work to function as a solution.

PO Sample.fmp12

[bcooney]

Do you order a product from more than one vendor? If so, you'll need a join table ProductVendor. That is, a product cannot have a single id_vendor foreign key.

What is the workflow for ordering? Perhaps start at a product list and select products to order? Then, script the creation of Purchase Orders.

[rmanh]

Thanks siroos, my PO file has line items including part ID number , description, units, costs per unit, which is all related. I have added some files for this and test records, but not sure how this works. 

A PO has only one vendor thus only parts from that vendor will be in the line items. 

Actually I started out with FM Starting Point solution from about 2011 or 2012, and modified their Invoice solution as our Purchase Order. I have attached here. 

Purchase Orders Clone.fmp12

PO Sample.fmp12

[siroos12]

Hi rmanh,

It is going to be a bit tricky but I will try my best to explain it as clear as possible. 

 

You would need to add 2 tables to your file. 

1- SuppliersList ==> Fields : ID and SupplierName

2- ItemList ==> Fields : ID , ItemName and SupplierIDfk

(You may change the names the way that they make more sense to you.)

 

Make a relationship between the two tables via SuppliersList:ID = ItemList:SupplierIDfk (Allow create and delete records from Suppliers to ItemList)

On the other hand, you need to add field called SupplierIDfk into Line Items and Purchase Orders tables (your existing tables) and make a relationship like this: Line Items:SupplierIDfk=SuppliersList:ID .

 

Make a layout for Suppliers, put the SupplierName field on the layout and add a portal to show up records from ItemList. This is to add Items which are related to each supplier.

We have two options here to assign a Supplier to a PO.

1- Make a relationship between the two tables using SupplierName and then lookup the SupplierID.

2- Make a relationship between the two tables using SupplierID and then lookup the SupplierName.

 

I would prefer the second method, it is more complex but more appropriate way. Lets do it:

After making the relationship, on the PO layout, add the field Purchase Orders:SupplierIDfk and overlay it with Supplier_Company Field. Set Purchase Orders:SupplierIDfk as a drop down field and make a value list for it to show up the values from field ID . I know, it will show up the IDs instead of Suppliers Names but here is the trick, check the check box "Also display values from second field" and select the same table (SuppliersList) and set it to show values from SuppliersName  and check the check box called" Show values from second field only". By this, we actually have our drop down to show up Suppliers Names but it inputs the SupplierID into the relevant field. and based on the SuppliersID, SuppliersName will be looked up.

 

So far you made your proper structure for your file. Now add a button on the PO Layout and call it "Add". Assign below script to it. It sets the SupplierID as variable and add a line item for the PO then set the field Line Items:SupplierIDfk for us. At this stage if you set the ItemDescription as a drop down and make a value list for it to show up values from field LineItemsList:Item , it will only show up the relevant values for that specefic Supplier.

Here is the script:

Set Variable [ $SupplierID; Value:Purchase Orders::SupplierID ] 
Go to Portal Row [ Select; Last ] 
Set Field [ Line Items::SupplierID ; $SupplierID ] 
If [ Get(LastError)=0 ] 
Commit Records/Requests 
End If 
Go to Field [ Line Items::Product_Description ] [ Select/perform ]

I have modified your file to give you a better Idea of what I mean by above. Please check attached.

I hope the i could make it clear enough. And please mark the post as solved if it is

 

Regards,

 

 

 

Purchase Orders V1.fmp12

[rmanh]

Thanks Siroos, yes I believe it did, thank you. But the more I get into this, the more I see I will need going forward. 

btw, not sure how to mark "as solved"