Forum Discussion
MID error when using two calculated columns for integer parameters
Whole numbers :-( but I think you got it ... there are definitely some records where the value of the calculated columns are null/blank. I guess I can use the function to calculate on only those with valid entries by wrapping an outer function to test for a number?
I just confirmed that a blank being returned in a calculated column value will cause the error you posted. You can get around that by doing something like this:
USERNAME = MID([LOG_MESSAGE], if(isblank([USERNAME_START]), 1, [USERNAME_START]), if(isblank([USERNAME_LENGTH]), 1, [USERNAME_LENGTH]))
Not knowing what your data looks like, I just used the isblank to check to see if there is a blank value in the calculated column and then replaced the blank with a 1. Same with the length in the second parameter. You may have to pick other values, but that should prevent blanks from breaking your formula.
One additional point, as I continue to try and figure this out, it appears that it is using a Column as the LENGTH parameter that is causing the error with the mid FUNCTION.
Can anyone else try to use a MID function on their data and see if this is indeed a case / problem?
I just tried using calculated columns as both the start position and the length and it worked.
What are the data types of the columns you are using? Do you have sample data? Are the columns you are using for the start position and length ever null, blank, or text?
Whole numbers :-( but I think you got it ... there are definitely some records where the value of the calculated columns are null/blank. I guess I can use the function to calculate on only those with valid entries by wrapping an outer function to test for a number?
I just confirmed that a blank being returned in a calculated column value will cause the error you posted. You can get around that by doing something like this:
USERNAME = MID([LOG_MESSAGE], if(isblank([USERNAME_START]), 1, [USERNAME_START]), if(isblank([USERNAME_LENGTH]), 1, [USERNAME_LENGTH]))
Not knowing what your data looks like, I just used the isblank to check to see if there is a blank value in the calculated column and then replaced the blank with a 1. Same with the length in the second parameter. You may have to pick other values, but that should prevent blanks from breaking your formula.