web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / The [Lead Time Calcula...
Microsoft Dynamics NAV (Archived)
Suggested Answer

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

I have the same question (0)
  • sbolton Profile Picture
    sbolton 145 on at

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

  • Natalie Karolak - the BC Docs Librarian Profile Picture
    Natalie Karolak - t... 1,955 on at

    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

    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')

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    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]

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans