need a help in UD Query

[Former Member]

Hi experts

i had made around 7 UDT in A/R invoice

1. Base Rate

2. No Days in Month

3. No Days Worked

4. SOTR (Special Over Time Rate)

5. SOTT (Special Over Time Hours)

6. NOTR (Normal Over Time Rate)

7. NOTT (Normal Over Time Hours)

now i need to calculate these tables date and need to appear in Unit Price Feild

The calcuation is as follows

Base Rate / No Days in Month * No Days Worked  + SOTR (Special Over Time Rate)  *  SOTT (Special Over Time Hours) + NOTR (Normal Over Time Rate) * NOTT (Normal Over Time Hours)

the total amount should appear in A/R INVOICE " Unit Price" Table.

for this i had made a query also, but it is not working when am assigning to the "Unit Price Table"

But the when i run the query as normal wise, it returning the total

and this is in row wise.

The below attach is how i assign the Query

*******************************************************************************************************************************************************************************

SELECT         INV1.U_BasicRate, INV1.U_NOTHour, INV1.U_NOTRate, INV1.U_SOTHours,

                         INV1.U_SOTRate, INV1.U_TotalDaysInMonth, INV1.U_TotalWorkDays,

                          (INV1.U_BasicRate / INV1.U_TotalDaysInMonth * INV1.U_TotalWorkDays + INV1.U_NOTHour * INV1.U_NOTRate)

                         + INV1.U_SOTHours * INV1.U_SOTRate AS 'Total Invoice Rate'

FROM            INV1 INNER JOIN

                         OINV ON INV1.DocEntry = OINV.DocEntry

WHERE        'Total Invoice Rate' = $ [$38.14.0]

***************************************************************************************************************************************************************************

pls help me sort it out

Regards

[Former Member · Accepted solution]

Hi,

Try:

SELECT ($[INV11.U_BaseRate.number]/$[INV11.U_NoDayinMonth.number])*$[INV11.U_NoDaysWorked.number]+

$[INV1.U_NOTHour.number]*$[INV1.U_NOTRate.number]+$[INV1.U_SOTHours.number]*$[INV1.U_SOTRate.number]

Thanks,

Gordon

[Former Member · Accepted solution]

Hi gordon

Thanks for the support

do i need to add any Where condition along with this Query, or just simply add the FMS

Regards

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try without where condition.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

hi rajan

i was trying without  "Where" Condition

its getting an error, the same way is it INV1 OR INV11

I try with both, still some internal error msg showing

Regards

[kothandaraman_nagarajan · Accepted solution]

Is it possible to post screen shot window with UDF field?

[Former Member · Accepted solution]

Hi Rajan

Is this fine

regards

[kothandaraman_nagarajan · Accepted solution]

Thanks for posting screen shot. Do you have TV? if yes, send TV details to check it NOW.

[Former Member · Accepted solution]

Hi rajan

MY TV is ver 8.0

ID : 918 445 908

PW :2547

Regards

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT ($[INV1.U_BasicRate.number]/$[INV1.U_DaysinMonth.number])*$[INV1.U_TotalWorkDays.number]+

$[INV1.U_NOTT.number]*$[INV1.U_NOTR.number]+$[INV1.U_SOTT.number]*$[INV1.U_SOTR.number]

Thanks & Regards,

Nagarajan