Fixed-length calc. Help!

[LaRetta]

I just found out that I must bill electronically Thursday morning. Looks like I'll be up all night. Anyone please help! I have a calc which needs to create a 'fixed-length' line. But, certain fields vary in length and the fields are not lining up. Could someone take a look and figure out what I'm doing wrong?

Left(Client::Diag1 & " ",11) & Left(Client::Diag2 & " ",11) & Left(Client::Diag3 & " ",11) & Left(Client::CGAS & " ",7) etc.

If a field is blank, the whole line slides left instead of lining up in columns. Also, the dollars must display with 2-decimal and many display 85 instead of 85.00. I also need to convert diagnosis to ###.## and some are 314.7 -- I need to add an extra zero to the end. I have plenty of space to play with. The line can be as long as 2000 characters -- I just need to be sure they line up in columns when the calc field exports. I know this is probably easy for you all ... can you help me?

[kennedy]

Hmmm... reading your post as is, the problem is that your just padding with one space... you should be padding with 11 spaces.

However, being HTML, these posts ignore spaces. So, it may be just your post is messed up... but just in case:

Left(X, 11) returns at most 11 characters. To get *exactly* 11 characters, you need to pad X with 11 spaces and then take the left 11... here I will use "-" to mean a space " "...

Left(X & "-----------", 11)

or:

Left(Client::Diag1 & "---------- ",11) & Left(Client::Diag2 & "---------- ",11) & Left(Client::Diag3 & "---------- ",11) & Left(Client::CGAS & "------ ",7) etc.

FWIW.

[LaRetta]

So you're saying that the number at the end should be the TOTAL width of the column I want -- not the 'remaining space' between? If that's the case, why am I adding the "--------" at the end of each field? Thanks for helping me!

[CobaltSky]

Hi LaRetta,

To get the columns to line up, you will need to put at least as many spaces inside the quotes, as the number of characters specified for a particular input field. You will also need to turn off the checkbox for "Do not evaluate if all referenced fields are empty" at the lower left of the calculation dialog. Also make sure that the calculation result type is set to text.

To convert fields from 85 to 85.00 or 314.7 5o 314.70, try the formula:

YourValue & Right(".00", Position(YourValue & ".", ".", 1, 1) + 2 - Length(YourValue))

Just change wherever it says YourValue to reference the appropriate input field. Again, the result type should be specified as text.

[LaRetta]

Ohh, that's what I didn't understand about the spaces and the number! Thank you SO much! I will do it right now!

[LaRetta]

Thank you for responding. Yes, my spaces were omitted when I posted. I'm putting 11 spaces but the BillingLine calc continues to slide if a field is blank. They don't want zeros, they want blanks. I tried adding:

Case(IsEmpty(ConvDiag1),"-----------", Left(ConvDiag1 & "-----------",11) etc.

Where ConvDiag1 is a separate calc to convert the diagnosis to 2-decimal, with the following:

Diag1 & Right(".00", Position(Diag1 & ".", ".", 1, 1) + 2 - Length(Diag1))

I have made sure that all calcs are unchecked on "do not evaluate" and that all calcs are text; and these diagnosis' are all text fields. I have date fields which I converted DateToText and then used that ConvDate in the BillingLine calc. I also converted two number fields (dollars and CGAS) to NumToText then used THOSE ConvDollars& ConvCGAS calcs in the BillingLine. They all slide!

Oh, it's going to be a long night!

[CobaltSky]

Hi LaRetta,

Sorry to hear you're still wrestling with this.

It shouldn't matter whether the calcs are stored or unstored.

However it isn't clear from your descriptions where the problem lies. I've just placed some code into an empty .fp5 file here and it seems to be working fine.

I'm attaching a copy of the test file so you can have a look at it and see if you can get your file to match up to it.

I've included two fixed length calculatiing fields in the attached file. One is using the two-step process that you are using at present, in unstored calculation fields. The other is doing the same thing in a single step. They both produce the same result.

Hope this gets you closer to a solution?!

conversion.zip

[LaRetta]

Well, I see one thing I had wrong ... Your example for ConvDiag1 shows:

Case(IsEmpty(Client::Diag1),0, Client::Diag1 & Right(".00", Position(Client::Diag1 & ".",".",1,1)+2-Length(Client::Diag1)))

My diagnosis conversion calc shows:

Case(IsEmpty(Client::Diag1),"-----------", Client::Diag1 & Right(".00", Position(Client::Diag1 & ".",".",1,1)+2-Length(Client::Diag1)))

And, I don't even have a fixed length step 1. Step 2 looks exactly the same. I will correct my formulas right now!

[LaRetta]

One other difference I noticed. In your example, both FixedLength Step1 and Step 2 have the box checked for do not evaluate. I thought ALL the calcs should be evaluated if empty ... could that have been the problem? Geeze, I hope it's that simple! I'll let you know after I play with it.

[CobaltSky]

The "do not evaluate..." option is no longer relevant if the test you are using starts out with an IsEmpty( ) function.

So it should no longer matter, you've got IsEmpty tests in all your calcs. I suspect your problem lies elsewhere?!

Also, the fields in the file I sent you are not "Step 1" and "Step 2", but 1Step and 2Step. They are both doing exactly the same thing, but one is doing it in two steps, whereas the other combines those steps into a single calculation (working off the Diag fields directly) so that you sould dispense with the ConvDiag fields and just go straight from Diag to output text.

[LaRetta]

I suppose all smart people are in bed but I'm going to try. I am attaching my 'problem.' I apologize for the extra files, there are several critical relationships in this. I delete all but a handful of records.

Service is the db with the problem calc. The last 12 field definitions are the calculations. BillingLine is the total one-line calc to be exported. The first two fields in BillingLine are globals because the transmission will go to two places and the names will change. DiagSeq is another set of 1-digit numbers that need to appear and ConvDiagSeq is my attempt at converting them. But, if I can figure out the ConvDiag calcs and get them to line up in BillingLine, I can go through the remainder of columns. Diagnosis codes actualy come from ::Client db.

Anyway, I'm going to continue on this but I'll remain online in case one of you get's insomnia and reads this. This is a VERY new db, so I know it's ugly. And you'all probably won't need 12 fields to accomplish this ... but every time I tried to combine them, it kept flashing 'nasty' messages (basically telling me I should take up knitting and leave the design work to the smart people or something)

Blank fields must be blank, no zeros. And they must line up. Well one thing anyway ... in Service entry, the 'NewRecordAlert' works great (thanks Cobalt Sky). Maybe I'm just too tired, but I can't see the differences between your demo file and mine that would cause it to mis-align.

PainInTheButt.zip

[CobaltSky]

LaRetta,

Please have a look at the attached and see if it is an improvement?!

Service.zip

[LaRetta]

It looks exactly the same! What did you do!! I just deleted all my formulas and decided to start over ... I think I was looking at that mess too long to figure it out ... what did you do?

[LaRetta]

My adrenalin just shot sky high! My hearts pounding! Now, I'm WIDE AWAKE! And ... excited! I swear it's exactly the same!

[CobaltSky]

Nearly but not quite... In the early versions, you may well have had problems with the calculations. You have since fixed them, but you were not able to properly tell that they were fixed, because the field was displayed on the layout using a proportional font.

Proportional fonts use narrower widths for some characters than others (spaces in particular), so things don't line up. To get your existing calc output to start lining up, I changed the font to a mono-spaced (ie non-proportional) font - in this case, Courier.

[LaRetta]

Oh. Funny! Sorry to take your time on it. I never would have thought of that. Hmm, now I've deleted all my formulas ... and I need to put them back! No, instead I'll copy this Service and import my records back into it! Yay! Proportional fonts, huh? Go figure! Well, I SURE learned a bunch from this one! The pot of coffee's gone and I'm wide awake! I think I'll export to a .... uh, what? They didn't say. A dbf put's headers, right? I don't want headers, I just want the old-fashioned dos 'text file'. Well, I'll play with my export options!

Is it appropriate to say "I LOVE YOU?" Proportion fonts, huh?

[LaRetta]

Exporting as a tab-separated, DOS (don't format output) appears to be exactly what I need. No headers, no EOL's -- exactly what they asked for. Wow, since I'm awake and have the billing ready ... and since I have an hour before I need to start my day, I think I'll do my FAVORITE thing ... go check out 'eye candy' ideas in the Right Brain! Well, for goodness sakes, I'm an 'artist' not a mathematician! Obvious, huh?