+13Hi all,
I have two tables; purchase_header and purchase_line.
This is all ok, but I keep running (also with other situations, this is just an example).
When I select this Purchase_header line (displayed as 2), and i got to the Cost Lines tab (table Purchase_line).
Can I access this data? Because it is super needed!
I have a feeling this can be accessed; yet I can’t find it.
I would be very happy is this can be used.
Anyone out there that can shine a light in my dark world?
Alex
Best answer by Mark Jongeling
The expression column could get the value like this:
select t2.[column]
from purchase_header t2
where t2.account_id = t1.cost_account_idDo make sure you can only retrieve one value. If the query returns multiple rows, even if it's the same value multiple times, an error will be thrown.
I also suggest looking at the data model and see if this structure is the correct. The Primary key of purchase_line doesn't include the PK of purchase_header, but it does have a reference and both table also share a column called 'purchase_invoice_number’. That gives me the feeling that the number might be saved redundantly, but I could be wrong 
+23Hi Alex,
How would you like to use this data? The default/layout/context procedures do know the value.
Since it's used in the reference, the column is not shown in the Grid, as the value for every row would be the exact same. You mean you do want to see this column?
(Please list Questions in the Questions & Conversations subforum. The topic was posted in the ThinkStore subforum, that is where our users can share their created solutions and functionality. Thank you in advance. I have moved the topic for you to the appropriate subforum)
+13Well,
in this case I want to connect a purchase_line to the a purchase_header. In the end, it should be via drag&drop. But for know a task with suffice.
The same issue is in different places of the app, where I have tasks that will handle cargo. I am already on the record( (similar to the “2” here) but I can’t access it.
My co-workers all say it makes sense, but I don’t like to give in. I see the “2” in my screen, so I should be able to use it! But perhaps I’m being stubborn.
+23To be sure, you did assign the column to the Task parameter right?
or (since it's drag-drop it could)
Did you assign the column to the Task parameter where the behavior is 'Pass as value’:
+13I have not yet set up the drag&drop, since I don’t have it the way I want it right now. First, let's get it working, then make it look good.
But, no! I do not even have that column in the purchase_line, so I have not specified in the table task either. I can add it, but in the end, the tasks places all info needed in a different table ‘cost_lines’.
So, I had, didn’t use it, removed it.
If I add it, will it function as a sort of dummy-column? Only to support this?
+23If I add it, will it function as a sort of dummy-column? Only to support this?
Yep, you can create is as expression column (for example). This will mean the column won't be stored on the database.
+13I have added the column and specified it in the table task. But now it can show the value from the purchase_line table. This column is currently empty. Can I pass the value from the Purchase_header to the Purchase_line without any difficulty?
+23The expression column could get the value like this:
select t2.[column]
from purchase_header t2
where t2.account_id = t1.cost_account_idDo make sure you can only retrieve one value. If the query returns multiple rows, even if it's the same value multiple times, an error will be thrown.
I also suggest looking at the data model and see if this structure is the correct. The Primary key of purchase_line doesn't include the PK of purchase_header, but it does have a reference and both table also share a column called 'purchase_invoice_number’. That gives me the feeling that the number might be saved redundantly, but I could be wrong
+13I have set it up like this, and I do see it, but the problem is, I do have multiple records in the purchase_header. So it does throw an error when i add more purchase_header.
I have a feeling this is all a lot of work for getting a value that is right there on my screen… Or am I wrong?
I am not sure why the PK is set up like this. I might ask my co-worker how initially build it… if he still remembers… I’m not familiar enough with this stuff.
+23The expression column can show the value of cost_account_id by using the query for the expression column:
t1.cost_account_id
+13Well, i’ve modified a lot.
I changed the relation between the two tables, and i’m getting in the direction of where I want to be.
So, now I have what I want when it is set.
However, when i open purchase_header and go to the detail_tab so see the purchase_line… it’s not thre (obviously..) Since it is not set.
And what you can’t see, can’t be handled.
When i set this manually in the Db, I see it, and it is as it should be. But I need to set it first.
Can I somehow show the purchase_line table without the filtering on the purchase_header_id (but on the account) (as it was before)?
Hmm, somehow I still feel like connecting ‘things’ is difficult.
For now, I’m adding a new (linking) table → purchase_header_line.
I add both tables in the Pk from said table.
Am I on the right track with this train of thought?
+23You can create a Upgrade control procedure to fill the column upon upgrading your database to the next version. This Upgrade control procedure has one template containing an Update statement to update the purchase_header_id to the correct one based on a condition. Assign it to the After upgrade.
Then when upgrading, after all data has been inserted, the Upgrade control procedure template code is added to the standard Upgrade script at the bottom, updating the values to the correct values. That should do the trick.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.