Storing info permenatly from relationship

[grumbachr]

I need to be able to store information in one table based on the relationship permanently.

The two tables I’m working with are Users (Employees) the other table is Requests. Right now using a simple relationship based on a UserID I can show the User info in the Request table no problem. But, if the employee record is deleted from the User table then the record in the request table is missing info. One way I've gotten around this is to have fields in the Request table to "lookup" the user info and store it that way.

However, this doesn't allow me to Find user from the request table. Is there technique that I can use that will allow me to store the user information permanently with the request but still give me ability to use the same fields to find users from the Users table?

[swf]

Take a look at the attached file. It has both options. One set of field "lookups" the id and name of the user, storing it at the time of entry and it will not change. The second set of fields are based on the relationship from the user table and if you change the user or delete them the related table data will change or be deleted. It shows the two ways to use the relationship.

Best example of this is that an invoice has to reflect price at the time of the purchase whereas price list has to reflect current price.

Take a look at the file and record 4. In this case the user was originally carl and was changed to jack. However the lookup value says carl but the related value now shows jack.

record.fp7.zip

[bruceR]

I need to be able to store information in one table based on the relationship permanently.

One way I've gotten around this is to have fields in the Request table to "lookup" the user info and store it that way.

However, this doesn't allow me to Find user from the request table.

This just isn't true, so I'm wondering what you are doing. Can you explain this a little more?

[grumbachr]

Sorry for the confusion.

Using "Looked-up Values" in the requests table I can automatically get my user information in a request record based on the UserID

What I looking for is a way to use those same fields, that perform the lookuped up values, to find user information that has not already been stored in a request record.

Example... I can have 500 users records in the user table I can enter find mode and find any of those 500 people. But from the requests table i where i only have 250 records for request and if each request is for a different person i only have half the user in the request table. so if i enter find mode in the requests table can't search on all 500 users. Can i search on all 500 users without going back to the Users table?

I think I'm asking the impossible.

[skrying]

You can do what you want just not how you want to do it. You can't search all users from the requests table, but if you have a relationship defined, and you have the user fields you want on the requests layout, you can search there.

Look at the attached file. Hopefully that does what you want.

record.fp7.zip

[T-Square]

You might want to examine your structure, based on the comment "Using 'Looked-up Values' in the requests table I can automatically get my user information in a request record based on the UserID". You see, unless there's a compelling *business* need to duplicate data from one table in another (in this case, User info in Requests records), you really should only store the UserID in each request. A compelling reason to duplicate information might be that you have to repeat the price of an item on the lines of an invoice so that you can track how much an invoice was in the past. Since you can search and display related fields, there is no compelling reason to use look-ups in this case (unless you have other, undisclosed points).

David