Lookup Issues!

[himesly]

I have a purchase order database that tracks vendors, purchasing information, and lineitem information. there are two main tables, Purchase Orders and Line Items. The purchase order table has a portal referencing the Line item database by the purchase order number. When the user goes in and adds a lineitem it creates a new record automatically filling in the purchase order field in the line item table.

This is where my problem starts. I have the vendor field in the line item table set as a look up. This look up is set to run when the purchase order field is changed. My problem is that it doesn't do that. When someone adds a portal record, the first record is correct. In any other records that get added the Vendor is not looked up even though in each record the Purchase order field is correctly filled in automatically. It is my understanding that because that field changed the look up should happen. I need to have this fixed because I have conditional value lists that reference the Vendor field.

Any help on this would be appreciated. I've tried everything I can think of and I don't know why this isn't working.

Jess

[mz123]

Would an auto-calculation that replace existing value solve your problem?

[Lee Smith]

Not in version 6. That option didn't exist in it.

I don't think I'm following your problem, but Auto Enter only works when the record is created in in your version. Take a look at the Relookup and see if that isn't what you need, or, maybe you can post a sample of your files.

Lee

Edited April 6, 2006 by Guest

[himesly]

Actually I am using FM Version 8. For some reason that didn't change in the box when I chose it.

Anyway, The problem is, when I make a new record through the portal, the reference is the PO number which automatically is created in the new portal record. However, the Vendor is a lookup that is based on the purchase order number. Any proceding record created through the portal does not lookup the vendor. This creates many problems as there has to be a vendor for other references to work in other parts of the program.

How exactally would i use an Auto calculation work? Wouldn't a look up be better as it has to reference a related record?

thanks for your help!

[mz123]

An auto-calc can do a similar function as a lookup.

Assuming the fields are as follows:

Table1::PurchaseOrderNumber

Table1::VendorAutoCalc

Table2::Vendor

Field calculation:

Table1::VendorAutoCalc (calculation) = Table2::Vendor

When you first create a record, and put in the Purchase Order Number, the Vendor should come up in the auto calc.

For existing records, you can run a "self" Replace Field Contents. '

Place the cursor in the Table1::PurchaseOrderNumber field, doesn't matter what record but make sure all records are showing.

Replace Field Contents of Table1::PurchaseOrderNumber (replaced through a calculation result)

The Calculation is....

Table1::PurchaseOrderNumber

Therefore, the value is not changed. Instead, it "replaces" the Purchase Order Number with itself.

Since it "refreshes" the purchase order number, the appropriate information should come into the auto-calc. (Be careful with this because you are replacing data and if you replace the data with anything other than itself, you will lose all old data... i recommend making a copy of your database just in case)

Does this make any sense or am I making this harder than it needs to be? :D