Portal to what table occurrence on layout based on what table occurrence?
Are all these one to many relationships as you move from Users to OrderItems?
Which would give you these relationships?
Users::UserID = Accounts::UserID
Accounts::AccountID = Orders::AccountID
Orders::OrderID = OrderItems::ID
Why aren't these fields defined in the Users table?
I had to fix the Users::UserID -> Accounts::UserID because it was many to many.
Now it is one to many relationship. So it is all one to many relationships as we go from Users to OrderItems.
When you say why these fields aren't defined in the Users table, what do you mean by that?
Just a question based on curiosity. The fields would seem to belong in Users.
Does it now work for you?
You'll need to modify your relationships or the layout to correct this. What's happening is that the data is in accounts but your layout refers to Users and the portal refers to order Items.
I think these your Relationships:
Users----<Accounts---<Orders---<OrderItems (---< means one to many )
When referring to fields more than one table occurrence away, you have to trace from table occurrence to table occurrence with great care to figure out why you get what you are getting.
Start with OrderItems, You have to go from there to Orders to Accounts to Users and then Back to accounts to get to the City field. That last step is from one to many records so you end up linking to the first Account record for that User--which apparently stores "Castle Hayne" in its city field. I predict that this portal's relationship enables the creation of related records. That would explain the 5th city showing in your portal. (in a sense, the layout is telling you that any new record will also display "Castle Hayne" on this Users record.)
To fix it, you could base your layout on Accounts.
You can also add these additional table occurrences:
and then add a field from Accounts2 to the portal row instead of from Accounts.
I think I solved the issue! I added a calculation field in the OrderItems called calc_city and pulled the field from Accounts::city.
Now when I put the merge field OrderItems::calc_city the data shows correctly!
I still don't understand why this is correct.. but at least it is working!
Thanks for your response Phil,
Well, what I did was your second option.
I added the AccountsPA::city but it is still repeating. (Now there was a "allow creation of records" in the ordersPA---<OrderItemsPA relationship and I removed it. Now it is not shoing that "1" extra row :) )
That's not my second option. I only posted the new stuff added to the right of the original table occurrences. The complete graph would look like this:
Select fields for your OrderItems portal from OrderTiems, Orders2 and Accounts2. Keep your layout based on Users.