1 Reply Latest reply on Oct 7, 2012 7:41 AM by philmodjunk

    Table relationship question

    ErnestChan

      Title

      Table relationship question

      Post

           I am making a simple inventory management file and I used portal to create the transaction items for a particular product (product code and lot number).

           So the product code and lot number are the keys between the master product table and the transaction table.

           My problem is that if I want to correct a wrong Lot Number of a existing product in the master product table, the transaction table will not change accordingly and the transaction record with wrong Lot Number would still exist and it would affect the calculation of product balance.

           Any way to make the transactions table Lot Number update if the Lot Number of the master product table is changed?

           Thanks.

        • 1. Re: Table relationship question
          philmodjunk

               I would suggest not using the lot number as the simplest way to avoid this. Use an internal serial number in place of the Product code and lot number combination so that changes to the lot number do not affect the behavior of your relationship.

               If you continue to use your lot number, you'll need to find the transaction numbers with the incorrect lot number, use Replace Field Contents to correct them first, then fix the lot number in your master product table. The main issue that will remain is that if your incorrect lot number results in a "match" to an existing combination of product code and lot number, this "fix" will update records that should not be updated and there is no obvious way to distinguish between the transaction records that should be changed and those that shouldn't.