Tracing back from purchline to main account and Financial Dimension values?

(1) Share

Report

Posted on by Ian Waring

436

I'm using Synapse Link for Dataverse tables in Power BI. I can see Purchase Order Line table /purchline/ records contain fields /default dimension/ and /Accounting Distribution Template/. I'm trying to reach the financial dimensions (we have Cost Center, Fund, Item Group and Project in our setup). However, the only table that appears to reflect a complete list of main account and each of the financial dimension values is table /dimension attribute value combination/. However, there is no apparent direct link between this table and purchline.

Table /dimension attribute value set (recid)/ contains only a subset of the dimensions used. /dimension attribute value set item (rec id)/ spits out a value for each dimension individually.

Any ideas how I get from purchline /default dimension/ or /Accounting Distribution Template/ to wherever I can pick up the dimensions to assemble into one posting string?

All responses (11)

Answers (1)

Verified answer

Martin Dráb 230,379 Most Valuable Professional on at

Like (0)

Report

Tracing back from purchline to main account and Financial Dimension values?

I'm not familiar with accounting distribution templates, but I guess you're looking for AccountingDistributionTemplateDetail table.
Ian Waring 436 on at

Like (0)

Report

Tracing back from purchline to main account and Financial Dimension values?

I've been at this on and off for 4 days now. I can get our four dimensions if purchline[defaultdimension] is populated - just get those out of dimensionattributevalueset via [recid]. If that's not populated (and on our system, that's more often than not), purchline[accountingdistributiontemplate] will point to accountingdistributiontemplate and allow to to fetch the template name - but I still can't work out how to get to the dimension values in each template. So i've parked it just to give the template names (english worded ones in column 2 below) until I can get hold of any ERD:

So, i'm still stuck. If anyone knows how to lookup dimension values in GL dimension templates, i'd really appreciate the name of the table I can find them in.

Thank you.
Ian Waring 436 on at

Like (0)

Report

Tracing back from purchline to main account and Financial Dimension values?

One step forward.

If purchline.defaultdimension is non zero, that fields content is the recid in dimensionattributevalueset.recid - and that gives access to our four dimension values.

If purchline.accountingdistributiontemplate is non zero, that fields content is the recid in accountingdistributiontemplate.recid

However, that record in accountingdistributiontemplate only contains the template name, and doesn't provide any of the corresponding dimension values in it. Any ideas what table those dimension values reside in?
Martin Dráb 230,379 Most Valuable Professional on at

Like (1)

Report

Tracing back from purchline to main account and Financial Dimension values?

An order is not a result of a posting, such as a entries in general ledger created for an invoice journal or a packing slip. An order is used as an input for posting and you can, but don't have to, select some default dimension.
Ian Waring 436 on at

Like (0)

Report

Tracing back from purchline to main account and Financial Dimension values?

Thankyou - that's the strange thing. I thought that if you posted any purchase order, there would be dimensions associated with it that would make it into the purchase ledger, Hence curious how I address the gaps where defaultdimension=0. I wonder if I should pick the dimensions out of the template definitions if that is the case - which is the lookup into AccountingDistributionTemplate.recid. I'll try that...
Suggested answer

Martin Dráb 230,379 Most Valuable Professional on at

Like (1)

Report

Tracing back from purchline to main account and Financial Dimension values?

This is the relation from PurchLine:

If DefaultDimension is empty (zero), it means it doesn't have a reference to any record in DimensionAttributeValueSet table. You can't expect to find a record with RecId = 0, because that's not allowed at all.

Accounting distribution template is a different thing stored in a different table. Again, you can look at the relation in F&O:
Ian Waring 436 on at

Like (0)

Report

Tracing back from purchline to main account and Financial Dimension values?

Results in
Ian Waring 436 on at

Like (0)

Report

Tracing back from purchline to main account and Financial Dimension values?
Ian Waring 436 on at

Like (0)

Report

Tracing back from purchline to main account and Financial Dimension values?

Thankyou Martin.

We have Financial Dimension Templates set up on our system that are entitled thinks like "P1011 - Directorate Allocated Training" and contain a Cost Centre, Fund, Item Group and Project code - eg: C3525, U1000, NONE and P10011. "Posting String" is just me concatenating them with a dash between each (ie: "C3525-U1000-NONE-P10011").

I have a purchase order line on the system that uses the named template, but the Default Dimension (0) nor Accounting Distribution Template (5637149828) appear in the dimensionattributevalueset table. There is an instance of the right values in there (recid 5637164836) but that bears no relation to any values in the purchline record, hence me thinking there must be another hop, skip, jump around another table to get there.
Suggested answer

Mohamed Amine Mahmoudi 10,262 Super User 2024 Season 2 on at

Like (1)

Report
Tracing back from purchline to main account and Financial Dimension values?

Hi @Ian,

PurchLine is linked to DimensionAttributeValueSet using DefaultDimension

DimensionAttributeValueSet is linked to DimensionAttributeValueSetItem table usning RecId

DimensionAttributeValueSetItem table is having field called dispalyValue which will give you the financial Dimension value

e.g.

select * from purchline pl join DimensionAttributeValueSet davs on pl.DefaultDimension = davs.RECID join DimensionAttributeValueSetItem davsi on davsi.DimensionAttributeValueSet = davs.RecId where pl.RECID = 5637145326

Best regards,

Mohamed Amine MAHMOUDI