Formula to work out user ID based

[shabbarankers]

Hi,

Im trying to work out how I can get an user ID onto a report. The relationships on the tables have been setup and Im confused how to create the formula. I've created a FullName formula and what I would like to do based on the result of that formula is look up Text1 value on table User_Values based on the result of the Formula which is made up from an Employee table (first_name & last_name)

Can anyone help please?

 

[Charliy]

Not without knowing more about whot your data table look like.

 

[shabbarankers]

Thanks for the reply Charliy, here is how the tables are linked

User_Values are custom fields within the database, Ive used User_Values.Text1 to hold a value for an ID number which links to the Employee table. I know this because if I go into the applications front end within the employee section I can set a value which links to the User_Values.Text1 Table\field.

Is this what you mean when you ask that you need more detail on the data table?


Thanks

 

[IdoMillet]

Are you asking how to get the Windows User ID (of the person running a report) into Crystal? There are 3rd-party User Function Libraries (UFLs) that add such a function. There are also 3rd-party Crystal Reports viewers that provide that functionality. See list at

http://kenhamady.com/bookmarks.html

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[shabbarankers]

Hi, no sorry I'm not trying to get the windows account onto the report I'm trying to get the user_values.text value based on the a formula which makes up the first name.last name from the employee table. Thanks

 

[shabbarankers]

Sorry that should be user_values. text1 I couldn't see where to edit the post

 

[IdoMillet]

One option is to create a subreport and pass the formula from the main report as a link.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[shabbarankers]

Thanks for the suggestion, Ive just watched a video on sub reports but Im not entirely sure how this would achieve what Im trying to do. I need something like a dlookup to lookup the value on the User_Values table where user_values.text1 is equal to the link value which joins the Employee from the Employee table :S

 

[pmax9999]

Maybe if you explained what you are trying to achieve someone will be able to help you.

Cheers
Pete

 

[shabbarankers]

I thought I had in my second post, but anyway. Just incase I didn't explain it properly.

Based on the above image I have 2 tables which are joined as above. What I am trying to do is get a value which is stored within User_Values.Text1 that links to the Employee table, specifically the Employee_firstname & " " & Employee_Lastname. The problem is because this field within that table can hold different values linked to different tables Im not too sure how I achieve this?

I think I've covered the question properly? Thanks

 

[pmax9999]

No, still not certain I understand the problem.

Are you saying that {User_Values.Text1} will hold more than just the User ID for a particular Employee Record and that there will be more than 1 record in the User_Values table for each Employee record? If that's the case, what is it that identifies a particular record as being the User ID, ie is it one of the other columns in that table that can be used to identify the appropriate row?

If that isn't the issue, perhaps you could provide some sample data and the result you are looking for so help us understand.

Cheers
Pete

 

[shabbarankers]

Hi Pete,

That's correct "{User_Values.Text1}" will hold more than just the User ID for a particular Employee Record. It also stores the record for all the separate tables which relate to Text1 for example Customer, Customer Invoice (as shown in the image below) and also Text 2, Text 3 etc for say the Employee record and other tables too

Im not 100% sure what identifies the link between the two tables (but based on the auto link within Crystal reports, its linked by the User_Values field as they are the only two similar columns within the two different tables). But browsing the data stored within these two columns the data isn't the same. You'll have to excuse my ignorance as Im still getting to grips with Crystal reporting (have more experience with sql\access). But I really do appreciate any help on this.

Is there a better\easier way to tell how the tables are linked other than within Crystal and looking within Database Expert in the links tab. Like I say I've browsed the data to see if the values it stores are linked to find a common denominator.

 

[pmax9999]

It is impossible to say whether User_Value column is the correct one to join on, without knowing the data. The same applies to being able to advise what is required to return the User ID field. As a test, I would recommend creating a report using the above two tables, and add these columns to the details section:
[ul]
[li]{Employee.Employee}[/li]
[li]{Employee.First_Name}[/li]
[li]{Employee.Last_Name}[/li]
[li]{User_Values.UserValues}[/li]
[li]{User_Values.Text1}[/li]
[/ul]

Does the report return data? If not, amend the join from an "Inner" to a "Left Outer". Does this change things?

Then sort the records on {Employee.Employee} and review the data. How many records are returned for each Employee? If it is just one, and {User_Values.UserValues} is the User ID you are looking for, problem solved. If there is more than one, add the rest of the columns from the User_Values table to the report. Do any of these fields contain anything to identify what data is stored in {User_Values.UserValues}? If not, is there anything about the User ID in {User_Values.UserValues} that could be used to uniquely identify a User ID (field length, field composition etc)?

If this doesn't get you where you need to be, please post some sample data so we can assist further.

Cheers
Pete

 

[shabbarankers]

Cheers Pete, I literally put into the report what you suggested to get the values and it worked. I think what I am doing wrong is trying to put all values into the report rather than doing it piece by piece, having some values which are incorrect are nulling the report and that is why I think it hasn't worked - to a point its true.

Thanks again and onto the next task