How to connect the table pod_det and (gltr_hist | ac_mstr | cc_mstr)

[nacho]

Hi everyone,

I am trying to connect the table "pod_det" with the tables "gltr_hist", "ac_mstr" and "cc_mstr", but I don't know how to do this. I search in the entity diagrams reference, but there is no way to do this, either in a direct way or using another table in between.

What I want to do is showing the "pod_desc" field in a browser that use the tables above, so I have to add the "pod_det" table to the browser, and put the field "pod_desc" in the browser definition.

Anyone has tried to do something like this before?
Is there a way to do this, or is impossible to do, and why?

Thanks to you all.
Greetings,

 

[Kishor Chari]

Do you want to link receipts to GL?

 

[shivakumarpatil]

hi,

pod_det <-> tr_hist <-> trgl_det <-> glt_det <-> gltr_hist

for ac_mstr,
gltr_hist.gltr_acc = ac_mstr.ac_code

for cc_mstr,
gltr_hist.gltr_ctr = cc_mstr.cc_ctr

HTH,
Shiva

 

[nacho]

hi,

pod_det <-> tr_hist <-> trgl_det <-> glt_det <-> gltr_hist

for ac_mstr,
gltr_hist.gltr_acc = ac_mstr.ac_code

for cc_mstr,
gltr_hist.gltr_ctr = cc_mstr.cc_ctr

HTH,
Shiva

Hi both of you. Thanks for your replies.

Shiva, I already have the binds for ac_mstr and cc_mstr, cause they were already there in the browser that i want to modify.

I follow the path that you described, but then the browser doesn't show any results.

The path that I setup is:

gltr_hist.gltr_line = glt_det.glt_line
gltr_hist.gltr_rflag = glt_det.glt_rflag

glt_det.glt_ref = trgl_det.trgl_gl_ref

trgl_det.trgl_trnbr = tr_hist.tr_trnbr

tr_hist.tr_trnbr = pod_det.pod_nbr

When I run the browser, it says that "no registers has been found". What coud be the problem with this?

I have the "QAD ERP 2 Technical Reference: Entity Diagrams" document. This document contains all of the tables in the QAD databases, or out there is a more complete document of that?

Thanks a lot.
Greetings!

 

[nacho]

Do you want to link receipts to GL?

What I want do is show up in a browser the pod_desc field (Purchase Orden Description). The browser already use the gltr_det, ac_mstr and cc_mstr tables to show up the information. I don't know the browser name in english, cause this QAD is setup in spanish; I supposse is something like "Applied Transactions Browser" (QAD option 25.13.23).

Thanks a lot.

 

[shivakumarpatil]

Hi both of you. Thanks for your replies.

Shiva, I already have the binds for ac_mstr and cc_mstr, cause they were already there in the browser that i want to modify.

I follow the path that you described, but then the browser doesn't show any results.

The path that I setup is:

gltr_hist.gltr_line = glt_det.glt_line
gltr_hist.gltr_rflag = glt_det.glt_rflag

glt_det.glt_ref = trgl_det.trgl_gl_ref

trgl_det.trgl_trnbr = tr_hist.tr_trnbr

tr_hist.tr_trnbr = pod_det.pod_nbr

When I run the browser, it says that "no registers has been found". What coud be the problem with this?

I have the "QAD ERP 2 Technical Reference: Entity Diagrams" document. This document contains all of the tables in the QAD databases, or out there is a more complete document of that?

Thanks a lot.
Greetings!

Hi,

i have this document
"MFG/PRO eB2.1 Technical Reference Entity Diagrams"
78-0603A
MFG/PRO eB2.1
September 2003

HTH,
Shiva

 

[nacho]

Hi,

i have this document
"MFG/PRO eB2.1 Technical Reference Entity Diagrams"
78-0603A
MFG/PRO eB2.1
September 2003

HTH,
Shiva

I have this:
QAD Enterprise Applications 2008 Standard Edition
Technical Reference Entity Diagrams
78-0728A
QAD 2008 Standard Edition
March 2008

I still can't see any results with the path that you described. I think I have to put some "outer joins" in between the tables. Any ideas?

Greetings,

 

[shivakumarpatil]

Hi both of you. Thanks for your replies.

Shiva, I already have the binds for ac_mstr and cc_mstr, cause they were already there in the browser that i want to modify.

I follow the path that you described, but then the browser doesn't show any results.

The path that I setup is:

gltr_hist.gltr_line = glt_det.glt_line
gltr_hist.gltr_rflag = glt_det.glt_rflag

glt_det.glt_ref = trgl_det.trgl_gl_ref

trgl_det.trgl_trnbr = tr_hist.tr_trnbr

tr_hist.tr_trnbr = pod_det.pod_nbr

When I run the browser, it says that "no registers has been found". What coud be the problem with this?

I have the "QAD ERP 2 Technical Reference: Entity Diagrams" document. This document contains all of the tables in the QAD databases, or out there is a more complete document of that?

Thanks a lot.
Greetings!

Hi,

The Purchase Order Receipts / Returns has to be done if the data should be there in tr_hist.

The IC transactions created from Receipts / Returns must be posted to GL if the data should be in gltr_hist. If not posted you can check the data in glt_det.


Please use the below links and check where you get and data,

for pod_det and tr_hist :
tr_domain = pod_domain
tr_nbr=pod_nbr
tr_line=pod_line
tr_type="rct-po" | "iss-prv"

for tr_hist and trgl_det:
tr_domain = trgl_domain
tr_trnbr = trgl_trnbr

for trgl_det and glt_det:
glt_domain = trgl_domain
glt_ref = trgl_gl_ref

for glt_det and gltr_hist:
glt_domain = gltr_domain
glt_ref = gltr_hist
glt_rflag = gltr_rflag
glt_line = gltr_line

HTH,
Shiva

 

[shivakumarpatil]

Hi both of you. Thanks for your replies.

Shiva, I already have the binds for ac_mstr and cc_mstr, cause they were already there in the browser that i want to modify.

I follow the path that you described, but then the browser doesn't show any results.

The path that I setup is:

gltr_hist.gltr_line = glt_det.glt_line
gltr_hist.gltr_rflag = glt_det.glt_rflag

glt_det.glt_ref = trgl_det.trgl_gl_ref

trgl_det.trgl_trnbr = tr_hist.tr_trnbr

tr_hist.tr_trnbr = pod_det.pod_nbr

When I run the browser, it says that "no registers has been found". What coud be the problem with this?

I have the "QAD ERP 2 Technical Reference: Entity Diagrams" document. This document contains all of the tables in the QAD databases, or out there is a more complete document of that?

Thanks a lot.
Greetings!

Hi,

tr_hist.tr_trnbr = pod_det.pod_nbr
change this to

tr_hist.tr_nbr = pod_det.pod_nbr

HTH,
Shiva

 

[nacho]

Hi,

The Purchase Order Receipts / Returns has to be done if the data should be there in tr_hist.

The IC transactions created from Receipts / Returns must be posted to GL if the data should be in gltr_hist. If not posted you can check the data in glt_det.


Please use the below links and check where you get and data,

for pod_det and tr_hist :
tr_domain = pod_domain
tr_nbr=pod_nbr
tr_line=pod_line
tr_type="rct-po" | "iss-prv"

for tr_hist and trgl_det:
tr_domain = trgl_domain
tr_trnbr = trgl_trnbr

for trgl_det and glt_det:
glt_domain = trgl_domain
glt_ref = trgl_gl_ref

for glt_det and gltr_hist:
glt_domain = gltr_domain
glt_ref = gltr_hist
glt_rflag = gltr_rflag
glt_line = gltr_line

HTH,
Shiva

Hi Shiva, thanks a lot for your replies.

I still don't see any registers in the browser. I do the links that you posted, but I don't have the "gltr_hist" field in the gltr_hist table, so I can't do the glt_ref = gltr_hist link. Instead, I made glt_ref = gltr_ref, that I suppose were the same, don't they?

I'm doing this in the "browser maintainer", but that would not make a big difference, right?

Thanks, greetings

 

[Kishor Chari]

ref CANNOT be in glt & gltr in the same time.

glt record whhen posted gets transfered to gltr hist.

 

[shivakumarpatil]

Hi Shiva, thanks a lot for your replies.

I still don't see any registers in the browser. I do the links that you posted, but I don't have the "gltr_hist" field in the gltr_hist table, so I can't do the glt_ref = gltr_hist link. Instead, I made glt_ref = gltr_ref, that I suppose were the same, don't they?

I'm doing this in the "browser maintainer", but that would not make a big difference, right?

Thanks, greetings

Hi,
I am sorry,
Please use the below links,

for pod_det and tr_hist :
tr_domain = pod_domain
tr_nbr=pod_nbr
tr_line=pod_line
tr_type="rct-po" | "iss-prv"

for tr_hist and trgl_det:
tr_domain = trgl_domain
tr_trnbr = trgl_trnbr


Use this link if the transaction is not posted
for trgl_det and glt_det:
glt_domain = trgl_domain
glt_ref = trgl_gl_ref


Use this like only if the Transaction is posted
for trgl_det and gltr_hist:
trgl_domain = gltr_domain
trgl_gl_ref = gltr_ref


HTH,
Shiva

 

[shivakumarpatil]

Hi Shiva, thanks a lot for your replies.

I still don't see any registers in the browser. I do the links that you posted, but I don't have the "gltr_hist" field in the gltr_hist table, so I can't do the glt_ref = gltr_hist link. Instead, I made glt_ref = gltr_ref, that I suppose were the same, don't they?

I'm doing this in the "browser maintainer", but that would not make a big difference, right?

Thanks, greetings

Hi,

mail me the code to patil.shivkumar@gmail.com
i will have a look.

HTH,
Shiva

 

[nacho]

Hi,
seems like the join that do the trick was:

gltr_ref = trgl_gl_ref
tr_trnbr = trgl_trnbr
pod_nbr  = tr_nbr
pod_line = tr_line
tr_type  = "rct-po" | "iss-prv"

Now I can see the pod_desc field in the browser. The only problem is that the browser is extremely slow now.

Thanks a lot for your help, both of you.
If you know some kind of optimization for searches likes this one, please let me know.
Thanks again. Greetings,