Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / The [Lead Time Calcula...
Microsoft Dynamics NAV (Archived)

The [Lead Time Calculation] field within the "Item" database table

(0) ShareShare
ReportReport
Posted on by Nathan Lykken 75

I have a calculated field in Microsoft Dynamics NAV that I need to display via a website for lead time for Parts in one of our databases.
 
This particular field is the [Lead Time Calculation] field within our "Item" database table.  In Navision, this field displays as a number followed by either the letter D (for day(s)), the letter W (for week(s)), the letter M (for month(s)), the letter Q (for quarter(s)), the letter Y (for year(s))
or the letters WD (for work day(s)).  For example, if the [Lead Time Calculation] value is 150D, this translates to be 150 days and if the [Lead Time Calculation] value is 150WD, this translates to be 150 work days. 

 

The problem is that in our SQL database table, these values of D, W, M, Q, Y and WD all display as the character: . So 150D on the Microsoft Dynamics NAV side ends
up being 150 on the SQL side and 150WD on the Microsoft Dynamics NAV side also ends up being 150 on the SQL side. 

 

Can anyone help me figure out how to convert this character back to its proper value of either D, W, M, Q, Y and WD within our SQL database? 

 

Please let me know.

 

Thanks,
Nathan

*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: The [Lead Time Calculation] field within the "Item" database table

    I realize this is terribly late but hopefully it helps someone else with the same issue down the road. The SQL below should get you what you are looking for, as well as show you the characters for each code.

    ,case

    when right([Lead Time Calculation],1) = char(2) then concat(left([Lead Time Calculation],len([Lead Time Calculation])-1),'D')

    when right([Lead Time Calculation],1) = char(4) then concat(left([Lead Time Calculation],len([Lead Time Calculation])-1),'W')

    when right([Lead Time Calculation],1) = char(5) then concat(left([Lead Time Calculation],len([Lead Time Calculation])-1),'M')

    when right([Lead Time Calculation],1) = char(7) then concat(left([Lead Time Calculation],len([Lead Time Calculation])-1),'Y')

    end as [Lead Time]

    --

    ,case

    when [Lead Time Calculation] is null then null

    when [Lead Time Calculation] like '' then null

    when right([Lead Time Calculation],1) = char(2) then cast(left([Lead Time Calculation],len([Lead Time Calculation])-1)as int)

    when right([Lead Time Calculation],1) = char(4) then cast(left([Lead Time Calculation],len([Lead Time Calculation])-1)*7 as int)

    when right([Lead Time Calculation],1) = char(5) then cast(left([Lead Time Calculation],len([Lead Time Calculation])-1)*30 as int)

    when right([Lead Time Calculation],1) = char(7) then cast(left([Lead Time Calculation],len([Lead Time Calculation])-1)*365 as int)

    end as [Lead Time Days]

  • sbolton Profile Picture
    sbolton 145 on at
    Re: The [Lead Time Calculation] field within the "Item" database table

    I was able to figure out days and weeks, the characters 1 and 3 i can not figure out since there is none in my system, but this seems to work, and if a situation pops up the msg contact admin will show up in the data

    [Lead Time Calculation], 

    REPLACE(REPLACE(REPLACE(REPLACE([Lead Time Calculation], 

    CHAR(1), '?ContactAdmin'), CHAR(2), 'D'), CHAR(3), '?ContactAdmin'), CHAR(4), 'W')

  • Natalie Karolak - the BC Docs Librarian Profile Picture
    Natalie Karolak - t... 1,955 on at
    Re: The [Lead Time Calculation] field within the "Item" database table

    I am not sure whether I got you right - does this issue affect the original item field or rather a copy of it in another table? If a copy is meant, are we talking about a normal field or a lookup flow field?

  • sbolton Profile Picture
    sbolton 145 on at
    Re: The [Lead Time Calculation] field within the "Item" database table

    I am searching for this same answer about a year later... Has anyone solved this mystery

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans