New Ticket Existing and Public Tickets   
Ticket:  417  Created: 9/14/2015 12:00 AM
 
Public/Private:
Status:    Assigned:      
Lean Application / Version:
Priority:       Due Date:  
Name:   Andrew Miller    
Email:   -
Phone:    Estimate Hours:  
Description:      
  Start:    Complete:  

Visible to Requestor Comment User Time
Select Hi again Andy - to answer your second question - the solution is to use the option 'Excel validation' for the field in question and using 'Custom' (formula) validation to determine the Valid condition. Lets say you have Employee Status in column G and 'Benefits Offered Date' in Column H. You would add a validation rule to the 'Benefits Offered Date' of: =OR(AND(G3 = "Full Time", H3 <> ""), AND(G3 = "Part Time", H3 = "")) If a validation formula evaluates True,then excel/edt treats the data as valid. To explain how this works, the formula is testing for either of two valid conditions : AND(G3 = "Full Time", H3 <> "") this tests that the employee status is Full time and there is a value in the Benefits offered column. AND(G3 = "Part Time", H3 = "") this tests that the employee status is Part time and there is not a value in the Benefits offered column. If either of the above is true then we have a valid condition, so we OR them. Please see second screen shot attached, note it is important to UNCHECK the 'Ignore blanks' option. Note that the formula specifies the starting row of 3, EDT will first apply the formula on the first row of data (row 3) then expand the formula to whole sheet of data. See in the attached screen shot that you also set the Validation message as appropriate. Let us know if you want to share screens to look at this some more. Lean Software 9/14/2015 7:42:39 PM
Select Hi Andrew, to answer your first question - set the column format to be 00000 for example (see attached screen shot). Then tick the 'Merge Number Format' option : this ensures the formatted value is sent to the database rather than Excel's underlying plain value. Lean Software 9/14/2015 7:13:31 PM
Select Hello Lean Team, I had a question about maintaining Leading Zeros when transmitting to the database. For example, I have a Social Security Number which begins with 1 or more zeros (ie '001234567'). In Excel, I can keep the zeros if I format the cell as Text, and copy/paste elsewhere will still keep the zeros. However, in EDT, I have noticed that even if the cell Displays the cell contents with leading zeros, the Value of the cell excludes the zeros, and this number is passed to the database without the leading zeros present. My question is: How do I maintain the leading zeros when sending data to the database? I have tried formatting the column as text, as varchar, etc. but to no avail. Any assistance you can provide would be most helpful! Aside from this idea, I have a second question. My project requires a certain level of Relational Conditions. I'm dealing with Employee Data, so something like Social Security Number or Date Of Hire must always be present in every row. However, other things like 'Benefits Offered Date' is not necessarily required in every row. For example, if the Employee is "Full Time", then I want to require a "Benefits Offered Date", or if this value is missing - throw an error. Conversely, if the Employee is "Part Time", I do not require an Offer of Benefits. I've been able to have the task throw an error if a cell value is not an exact length like "SSN" is not 9 digits, or if it contains letters, etc ... Also, I've been able to make a field Required, this is easy. But to require a value conditionally based on the value of a different column escapes me. Could you help me understand how to achieve this goal? Thank you very much for your assistance, We have great faith in your team and your product! Best, Andrew BCA Requestor 9/14/2015 4:12:42 PM

Tags:

Skip Navigation Links.
Bug report
Consultancy
Collapse DatabaseDatabase
Access
MySQL
Oracle
Other
SQL Server
Feature request
Feedback
Help request