1 portal - multi user - how to alter foreignkey ?

[kenannet1]

Hi there,

I'm trying to design a portal with 20 different users, each user(sales-person) has own specific clients. For example User 105 can only access the fields with the user=105 identified.

I have a global primarykey to start the search in the portal that can take any of (Name / Refrence No /Phone No of clients) in characters. Portal Data includes "Name", "Refrence" and "Phone No" and grabs the related data for each logged users and stores in a different field i.e. Name105, Refrence105 and Phone105 because related data is in a different (child) file so that calculation field in the Portal grabs different data according to the user logged on and the shows the data with the help of "Case" function.

I have a ForeignKey which is a calculation field and the grabs indexed data from the fields of Foreign105, Foreign106.........Foreign125 with the help of "Case" function.

For example ForeignKey for user110 (Foreign110) would be as follows so that it can show the search results in the portal and retrieve the related record on the side of the portal.

Left(name110,1) & "

[Kurt Knippel]

Ok, just so that we are talking the same lingo:

One -> Many

PrimaryKey -> ForeignKey

What do you actually want to happen. It seems like your example above shows that you want to restrict "Fields" based upon some kind of calculation? Do you mean that you want to restrict "Records"?

What I think that you want to do is to show a portal based upon UserID and that is it.

[kenannet1]

Thank You for your Reply

PrimaryKey is a global field where the user can enter (first characters 5 to 10)her/his user criteria to find the matching records specified in ForeignKey. Eventually, as users starts to log on ForeignKey will do the calculation for the new user and start listing records on the Portal. Here is the open form of the calculation for better understanding:

[ForeignKey]

Case(

userid=110,

ForeignKey110,

userid=127,

ForeignKey127...

)

If user110 logs on and enters Name as a "David" Portal will show the values belong the user110. However when a different user logs on say user127 and if he has another client named "David" portal will show both "David"s listed for both user110 and user127. I'm looking for a way to stop this without duplicating the layouts (for each user).

Thanks,

Ken

[Kurt Knippel]

Ok, you DO need a "filtered portal". First erase that whole CASE thing from your mind. You do NOT need it and it is largely what is screwing you up.

Basically you need to define a field (global) to store your search criteria. Then you need a calculated portal filter on the left side (primary key) of the relationship, and a calculated compound key on the right side (foreign key). When you fill in the globals (either through user interaction or automatic means) it will filter the portal.

Assuming:

MainMenuDB.fp5

gUserID

gSearchCriteria

cPortalFilter = gUserID & "-" & gSearchCriteria

Relationship - MainMenuDB.fp5::cPortalFilter - ContactsDB.fp5::cCompoundKey

ContactsDB.fp5

UserID - the ID of the user who owns the records

Name -

Reference

PhoneNumber

cCoumpoundKey =

UserID & "-" & Left(Name,1) & "

[bruceR]

It is difficult to understand what you are trying to do, but typically fields like Name100, Name101, Name102 suggest a serious fundamental design problem. You need to go back to square one and create a properly normalized database.

[kenannet1]

That was awesome!

Incredibly smart, and it works perfectly

I do not know how to thank you

Ken

[kenannet1]

note:

My thank you note was for Kurt Knippel

Thanks again,

Ken