Jump to content
  • 0
Sign in to follow this  
kme

Populate Datapage Column According To A Separate Table's Field

Question

[Please skip to the first comment I made]

 

I have a slightly complicated procedure I'd like to implement.

 

DataPage D's data source is from View V

In the Results Page of DataPage D, I'd like to include a field F that's from Table T

But Table T is not included in View V.

 

This is a sample Table T:

 

[iD] [fieldF]

1        A

1

1        B

2

2        C

2

2

3

3

 

This is a sample View V, according to Table T:

 

[iD] [fieldF]

1       A

1       A

1       A

1       A

1       A

1       A

2       C

2       C

2       C

3

3

3

3

3

3

 

ID from Table T = ID from View V

 

Here is how field F in View V is inputted:

The top most field F value for ID 1 is A, so all field F for corresponding ID's will be A.

The top most field F value for ID 2 is C (blank is not included), so all field F for corresponding ID's will be C.

There are no field F value for ID 3, so all field F for corresponding ID's will be blank. 

 

Periodically the top most row will be removed, and then new values will be added, etc. though I'm not sure if that really matters.

 

Now how do I do this?

 

Here are some problems I'm thinking:

- There needs to be a way to link Table T's ID with View V's ID, when Table T is not included in View V

 

Here are the steps I'm thinking:

1. In View V, create a new field/column for the Results Page, named fieldF

2. For each ID in View V, select Table T ID's field F

3. Loop through the field F values from top to bottom to search for the top most value, like I explained above. If there are no values, leave blank.

4. Populate all of the View V's field F for each ID

 

..How do you code this?! 

 

Thanks for any help 

Share this post


Link to post
Share on other sites

3 answers to this question

Recommended Posts

  • 0

This is the SQL that works, for Table A and Table B.

Table B is the data source for the DataPage, and Table A isn't. 
I want to update Table B's column "City" according to Table A's column "Letter".

 

TABLE A

+----------+-------+
| Number | Letter|
+----------+-------+
| 1           |   A     |
| 1           |          |
| 1           |          | 
| 2           |          |  

| 2           |          |  

| 3           |          |  

| 3           |   B     |
| 3           |          |
| 3           |   C     |
+----------+-------+

 

TABLE B

+--------+-------+
|   AC    |  City  |
+--------+-------+
| 1         |     A   |
| 1         |     A   | 

| 1         |     A   | 

| 1         |     A   | 

| 2         |          | 

| 2         |          | 

| 2         |          | 

| 2         |          | 

| 3         |     B   | 

| 3         |     B   | 

| 3         |     B   | 

+--------+--------+

UPDATE TableB
SET City = Letter
FROM
(
SELECT Number, Letter, ROW_NUMBER() OVER(PARTITION BY Number ORDER BY number ) AS SortOrder
FROM TableA
WHERE Letter IS NOT NULL AND Letter != ''
) AS A
WHERE SortOrder = 1 AND TableB.AC = A.Number;

Where do I add this code and format it for it to work in the DataPage?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...