What relationships do you have in place for these portals? A screen shot of Manage | Database | relationships may be very helpful. Do these tables use Serial ID fields for priimary keys?
I want to able to look up the price for every portal rows based on lot size on the sell price portal
I'm not sure what you mean by that. Is "lot size" the MOQ column in these portals?
Sorry, the lot size is the MOQ (Min Order Qty)
on the MOQ I will pop in the qty and to lookup the price from item price info table
I attached the relationships capture, the P keys are in serial ID
First thing that jumps out at me is that your two portals use exactly the same table occurrence reference: costLotHKD. That means that both portals refer to the same exact set of related records, though a portal filter could be used to show different subsets of that common set of related records.
What is not clear to me is the function of MOQ_calculation here. I can see that fields from it have been included in the portal rows: unit_price, c_sellPrice, but the relationship only matches by MOQ. Thus, if I had a screwdriver with an MOQ of 5 and a power drill with an MOQ of 5, I'd have different records in costLotHKD, but they would match to the same record in MOQ_calculation. That seems incorrect as I wouldn't expect two different parts to have exactly the same unit price--which is a field in MOQ_calculation.
I corrected the table occurrance to use MOQ_calculation, and I managed to lookup the price from costLotHKD, so when I enter the MOQ I have the correct price, but what puzzled me now is every portal row I created in MOQ_calculation a new portal row also created on costLotHKD automatically see capture attached.
The logic was if the MOQ match bring me the price, which works, but then new record also created on costLotHKD protal, the "allow creation of record" was not check on the left, I don't know why it created a new record on costLotHKD protal
All MOQ on MOQ_calculation are the same part, just the different MOQ qty for different unit price.
That's an automatic "side effect" of the relaitonships that you have here. Your layout is based on Part_Info. Creating a new record in the portal to MOQ_Calculation requires that a record with mathcing _kf_costHKD and MOQ values also be created in the costLotHKD or there would be no way the new record could be visible in the portal to MOQ_Calculation.
Note that this still creates a situation where two different items with the sam MOQ will match to the same record in MOQ_Calculation.
Thanks Phill, I may have to think of other approach to do the calculation