Finance | Project Operations, Human Resources, ...

Relation between ProjTransPosting and ProjPostTransView

(0) Share

Report

Posted on by ShaqAX

709

Hello everyone,

Need an advice. I'm looking for relations between table ProjTransPosting and ProjPostTransView, Can we relate this somehow ?

Reason being, I'm using table ProjTransPosting to get some invoice data, but then I need to consider whether the Project has ProjInvoiceStatus field is "Fully invoiced". Looking at menu Project management \ Transactions \ Posted Project Transactions, this is where I gonna take a look

When I trace back, that field is coming from View named ProjPostTransView. How is these two tables related actually, does it have the same granularity by means, if just join tables and add a custom column (in my custom view source by ProjTransPosting) from ProjPostedTransView.projinvoicestatus, does it relatable ?

Thanks in advance.

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi ShaqAX,

You can try linking based on the field "TransId".

Was this reply helpful? Yes No
ShaqAX 709 on at

Like (0)

Report

Hi Gunjan,

Thanks, the thing is I need to add this 'projInvoiceStatus' into my existing View. May I have your guidance how to do it ?

As mentioned I have View that the main data source is ProjTransPosting and as your advice, I added new data source to ProjPostTransView with relation of TransId.

As such, I would like to add that 'projInvoiceStatus' into my View's field. But this 'projInvoiceStatus' is actually not a field, it's a view method in table ProjPostTransView. How to bring this to my view ?

I tried to create a method as well in that View but I'm not sure it is correct. After adding a new String computed column using that View method, On Build or Sync, there is no error, but the new field has not come out

Thanks in advance

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi ShaqAX,

The field projInvoiceStatus is a display method. What is your exact requirement here? Do you want to add this field for exporting data or for display purposes only?

Was this reply helpful? Yes No
ShaqAX 709 on at

Like (0)

Report

Hi,

Yes, it is a display method. I want to add in my View as one of its field, and gonna use that view for my Data Entity with the final result is this data entity will be used in my Aggr. Measurement for my FACT table.

If I succeeded to add field 'projInvoiceStatus' to my View, the rest "I think" I can do it.

Thanks.

Was this reply helpful? Yes No
Suggested answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi ShaqAX,

To add it as a field, you will need to add a computed column and convert the same logic into a T-SQL statement. You can take a look at the view "ProjTransSaleView" and check if the field TransStatus helps you achieve your requirement.

Was this reply helpful? Yes No
ShaqAX 709 on at

Like (0)

Report

Hi Gunjan,

Did you mean "ProjPostTransSaleView" ? It has TransStatus but it is different.

Noticed ProjPostTransView with the view method "PostingInvoiceStatus" also look into TransStatus which are coming from enum ProjTransStatus, but also with condition / logic of other, so it is quite complex and it will look like I'm rewriting what's already available in ProjPostTransView.

Is there any sample View that I can refer to that showing how to add computed column from a display method of its data source ?

Thanks,

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

I don't see any standard view that has implemented a computed column for showing project invoice status for a transaction. If you want to take a look at how you can create a computed column for a view, you can check this post.

Was this reply helpful? Yes No
ShaqAX 709 on at

Like (0)

Report

Thanks, I will explore that post, only if I take a quick look, seems they also using the "physical" field of its data source rather then a display method provided from the data source.

Thanks again.

Was this reply helpful? Yes No
Gunjan Bhattachayya 35,423 on at

Like (0)

Report

For a computed column, you need to have a method that returns T-SQL code which returns the desired value. You can't use the logic used in a display method here as it uses X++ functions.

Was this reply helpful? Yes No