Jump to content

Storing info permenatly from relationship


grumbachr
 Share

This topic is 5651 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 3 months later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5651 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.