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

[former_member184146]

Hi Sahan,

               It should be there in list above the UoM Code.

and try the last query i gave to you.

Regards,

Manish

[Former Member]

Hi manish

Above UOM , its TYPE appear

Regards

[former_member184146]

Hi Sahan,

               what is your SBO version & PL??

Regards,

Manish

[Former Member]

Version:SAP Business One 8.82 (8.82.076) PL: 12

[former_member184146]

Hi Sahan,

               Can you give me the team viewer so that  we can look together at the issue.

Regards,

Manish

[Former Member]

is it possible now

[former_member184146]

Its your choice Sahan if you  have time then we can otherwise we can see tomorrow.

Regards,

Manish

[Former Member]

HI manish

my office is closing in next half an hour,, now we have our office only on Monday

So shall we sit on Monday Morning

if u r Convenient

Regards

[former_member184146]

Okay No Problem Shahan, have a good weekend.

Regards,

Manish

[former_member184146]

Hi Shahan,

                    Try the below FMS on Unit Price.

SELECT (($[RDR1.U_BaseRate.NUMBER]/$[RDR1.U_NoDayinMonth.NUMBER])*

$[RDR1.U_NoDaysWorked.NUMBER])+$[RDR1.U_SOTR.NUMBER]+$[RDR1.U_SOTT.NUMBER]+$[RDR1.U_NOTR.NUMBER]+$[RDR1.U_NOTT.NUMBER]

Regards,

Manish

[Former Member]

Hi manish

i had made the tables as per my DB and try to run

but am getting an error

Internal error (-1003) occurred  [Message 131-183]

Regards

[former_member184146]

Hi Shahan,

               Sorry i forget to update the RDR1 table with INV1,

Try this

SELECT (($[INV11.U_BaseRate.NUMBER]/$[INV11.U_NoDayinMonth.NUMBER])*

$[INV11.U_NoDaysWorked.NUMBER])+$[INV1.U_SOTR.NUMBER]+$[INV1.U_SOTT.NUMBER]+$[INV1.U_NOTR.NUMBER]+$[INV1.U_NOTT.NUMBER]

Regards,

Manish

[Former Member]

Hi manish

Still the same error Prompt

Internal error (-1003) occurred  [Message 131-183]

rEGARDS

[Former Member]

Hi,

Try this.

I just corrected some of the table names. Instead of INV11 it should be INV1

SELECT (($[INV1.U_BaseRate.NUMBER]/$[INV1.U_NoDayinMonth.NUMBER])*$[INV1.U_NoDaysWorked.NUMBER])

+$[INV1.U_SOTR.NUMBER]+$[INV1.U_SOTT.NUMBER]+$[INV1.U_NOTR.NUMBER]+$[INV1.U_NOTT.NUMBER]

[former_member184146]

Hi,

try this

SELECT (($[INV11.U_BaseRate.FLOAT]/$[INV11.U_NoDayinMonth.FLOAT])*

$[INV11.U_NoDaysWorked.FLOAT])+$[INV1.U_SOTR.FLOAT]+$[INV1.U_SOTT.FLOAT]+$[INV1.U_NOTR.FLOAT]+$[INV1.U_NOTT.FLOAT]

and make all your UDF's as Type : Units and Totals and Structure : Price

Hope this will help you

Regards,

Manish

[Former Member]

Hi MANISH

All the fields are as per you said is  Units and total - Type and structure is Rate

but still am getting the same error

pls find the attached files of the screen shot

i had tried with both , Number and Float

Regards

[former_member184146]

Hi Shahan,

                    On which field you are are auto refreshing your query????

and it should be on refresh regularly.

Regards,

Manish

[Former Member]

Hi manish

on item No, and it given as Refresh Regular

regards

[former_member184146]

Hi Sahan,

               Refresh it on Unit Price and try below query

SELECT ((convert(float,$[INV1.U_BaseRate.Float])/convert(float,$[INV1.U_NoDayinMonth.float]))*convert(float,$[INV1.U_NoDaysWorked.float]))+convert(float,$[INV1.U_SOTR.float])+convert(float,$[INV1.U_SOTT.float)+convert(float,$[INV1.U_NOTR.float])+convert(float,$[INV1.U_NOTT.float])

Regards,

Manish

[Former Member]

Hi manish

am Auto Refresh  as   When Exiting Altered Column

but Unit price is not seen in that

in the same way i change the Auto Refresh  to   When Exiting Altered Column

in which also Unit Price is not Seen

Regards

[kothandaraman_nagarajan]

Hi,

Please advice above fieds are UDF or from UDT.

Thanks & Regards,

Nagarajan

[Former Member]

opppssss,, sorrry Mr rajan

its UDF NOT UDT in A/R invoice Row wise

Regards

[kothandaraman_nagarajan]

Hi,

By using FMS query, you can achieve such requirement. To test, I need exact UDF field type for each UDF.

Try with Mr.Manish FMS just replacing RDR1 with INV1.

Thanks & Regards,

Nagarajan