You can't use VI to update data without the entire Primary key in the source data.

The really tricky part is fact that each PO can have multiple invoices, so even scripting a lookup (query, add fields to your input data...) may not be easy.

As Kevin said, the Primary Key fields would be required in order to even update a record.  Now you could run a script to process the file before it is read, this could be done by using the On Execution perform logic event to execute a VBScript file that does the processing or you could use the After Read perform logic event (which takes place immediately after a record is read from the source file) and use ProvideX to handle gathering the values you want, which you could either store in variables you reference when setting the scripts or by setting the IMP$[x] variable where x is the next unused column number in your source file.  You would need to make sure you are resetting either variable that you decide to use in the event a match is not found.  

If you use VBScript, you could query via ODBC to get the values you want or use the BOI.

If you use ProvideX, it would probably be best to use BOI and your own handle to the PO_ReceiptHistory_svc object where you could use either SetBrowseIndex, SetBrowseFilter, MoveFirst, MoveNext, and loop as needed until the EOF property is 1 OR you could use GetResultSets.  

If using BOI for either, it helps to have a basic understanding of scripting with Sage 100 BOI objects.

Then there is still the matter of dealing with multiple receipts against a single PO.  If you were pre-processing the file with VBScript, you could just add additional lines to your source file for each receipt record found, if using ProvideX, it might be possible to manipulate the variable that holds the contents of the source file that is read into memory but this could be tricky, never attempted it myself.

First, thank you for the quick response Kevin and David.

What I'm trying to do is basically find a matching record from an import file (source) to a record within a Sage table and, if the record matches, populate a field from that matching record within the source to the record in the table. I'll have the key information within the source data.

If the PO and Invoice had a one-for-one relationship (no multiple invoices to a purchase order) and the source has the primary key information, is it possible to do just a simple "match record, add field, look for next record to match, add field, etc.)?  Because I'm not changing any primary key information I'm not sure why an update is referenced in both of your responses...unless I didn't give enough information initially.

Thank you

Sage 100 tables can have single field primary indexes or multiple field primary indexes, in the case of a multiple field primary index, you MUST have each value for each field in that primary index in order to create/update a record.  Since you said your goal was to update, you must have values for all of the fields that Kevin highlighted.  If you are missing even one, it won't be able to update your intended record and depending on the object and table, may end up creating new records that are missing values in the primary key.  If you look at the file layouts and look at the last character in the column that I have indicated, this will tell you whether or not the field is required.  "Y" means required and "N" means not required.  Which to be honest, I'm surprised the HeaderSeqNo is not marked as required.

I don't know what you are doing in between the two companies so I can't say for sure that a HeaderSeqNo from the source company would always match the HeaderSeqNo in your destination company.  The field is a numeric string 6 characters long padded with zeros on the left.  So in theory, if a PO as only one receipt in the source, then the HeaderSeqNo should be "000000" which should also match in the destination company if there was only one receipt.  However, the receipt number in the source company likely won't match the receipt number in the destination company as both companies track the next receipt number to use separately and unless you are either importing the receipts in the destination company using the receipt number from the source company OR copying the data from the source company to the destination company, then it is possible that the next number tracked by each company could get out of sync.  This is why you would also need to retrieve the receipt number and receipt type or have them in your source file in the first place so the VI import can properly update the right record based on all of the fields that make up the primary key.

The PO # and Receipt of Goods # (ROG) will be the same...we will be using the first two digits of the PO # as the company identify, followed by the five characters as the remaining PO #. We will not be using the “Next” option for either PO or ROG #s. So, I think I can get away with padding the HeaderSeqNo with "000000".

The complexity of this is that we are uploading PO and ROG data from multiple companies within Sage to a third party. The third party is gathering additional data from a fourth party then downloading it back as completed invoices. We are then importing that file into a specific company as AP Invoice Entry..

Typically, you’d update the Receipt of Invoice at this point but we do not want to update the Receipt of Invoice within the multiple companies because the update will post information to AP, which is being handled in the specific company noted earlier. However, we use the PO Receipt History Report within each company to identify any purchase orders that don’t have an invoice number in order to track.

In order to confirm the PO Receipt History Report has the accurate information, we need to import the invoice number into the ROG History Header so it appears on the report (along with the Invoice Amount).

For the import into the ROG History table, I was planning on using multiple VI jobs (one for each company) and use the first two digits of the PO number to use as a Select. However, I was hoping there was a simple way to populate the invoice number into a matching record containing the same PO #.

If the receipts in the destination company are always done as a receipt of invoice OR as a receipt of goods (not a mix of both) AND the receipt number always matches the purchase order number, then you could in theory hardcode the receipt type to either "I" or "G" and then hardcode the header sequence number to "000000" and this would give you all the values for the fields that make up the primary key.  I'd still be weary of inconsistencies and you will want to make sure you thoroughly test this with copies of the production companies before you commit to running them in the production companies.

I am with you 100% on thoroughly testing.  However, I'm still at the point of not knowing how to implement this within VI.  I have a source file that contains 100 records all with different PO numbers.  I have a ROG History table that will contain some of those PO #s but not all.  If the source and ROG History table contain the same PO # I only want to populate the invoice number from the record of the source file to the matching record in the ROG History table then move onto the next record for matching.

Take your input file and run it through a script.  Query the table and find the record(s) that match the PO#, and build a new file including the original line data plus new fields from the query (to fill out the primary key of the table). 

Build whatever error checking you wish into the script (flagging missing / multiple matches).

Then use that second file for VI import.

Thank you again for the responses Kevin and David.  It sounds like what you are suggesting is filtering the records prior to importing, then import the results as an overwrite of information into the ROG History table.  Not the path I was hoping for but I appreciate the option.  I'll need to give this approach some critical thought to determine if this is the path we want to take.