Hello
Using Jet Essentials 2012 12.0.12150.0, Excel 2010.
My very first post here and I need help to the following:
Some of our users have inputted Serial Numbers for equipment starting with 0. Excel then cut off those 0 when I try to get the information with JET through to excel. Or when I copy / paste data from the NAV talbe into excel.
Example: No. 001 or 0001 or 01 they all become 1 and so we can't identify which resource is is.
We do have various reports (Function Wizard or Table Builder) where I need to get the correct resource number through incl. the 0 before the numbers (as text?).
In table builder I tried to add a Formula =NP("Formula","""ERP-""&[@[No.]]") to convert the numbers to text with a prefix of ERP- for all resources. Unfortunately this doesn’t work as it already takes the No. without the 0 when it calculates the the formual. So 0001 becomes ERP-1 instead of ERP-0001.
What can I do to get the full information back incl. all the 0 before the number? I need to be able to do this for Table Builder and Function Wizard reports.
Also, same table same field some users have used the serial numbers starting and/or ending with * (No. = **A** or *B* or C* ) - If I then try to link another table to it with NL("First"..) it of course consider the * as a wild character and it brings back the information for the first resource found with the letter A or B or first start with C etc. What I would need is that Jet checks the complete contence in the field No. **A** and bring back the information to this it. How can I overcome this so that Jet Essentials in the NL("First" looks up the complete string and not only a part of it and considers the * as a wild character?
Many thanks
Andy
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi Andy,
What I would need is that Jet checks the complete contence in the field No. **A** and bring back the information to this it. How can I overcome this so that Jet Essentials in the NL("First" looks up the complete string and not only a part of it and considers the * as a wild character?
I don't know of a way that can be done other than tell your team to not use the * character because it does have a very specific meaning when it comes to queries.
Even the other problem is going be very difficult to work around. You could create a CONCATENATE formulate like
=NP("Formula","CONCATENATE(""ERP-"",TEXT(Cust._Ledger_Entry[@[No.]],""00000000""))") but even that is going to format all of your 01, 001, or 0001 as the same eight digit value after so that doesn't really solve your problem.
You may need to implement some sort of IF statement that counts the number of places in the No. field, then use different CONCATENATE statements based on the count.
That said, in the table builder, it should list the No. however it is in your database. I have a customer number 014415544 and Jet outputs it as such. It doesn't remove the 0 from the front.
If you need to pull information about a specific Serial Number you could always use ="0001" to retain that exact match.
Please sign in to leave a comment.