Adding text multiline numbers

[LaRetta]

Hi everyone,

Well I know I could script-parse this puppy but I had the hair-brained idea I could use a calculation. So this isn't a desperate need to solve an immediate problem but I'm so irritated at my text-function disability that I really want to know what it might look like.

I have attached my simple working demo. Two tables: Products and Inventory. Data for a product in one text field (multiline) thus and I need to count them. 'e' is for each (simple); 'c' is for case quantity (taken from the Products table), ie, 46 * Products::QtyPerCase. As you'll see, I haven't gotten very far in this calculation (reasons below).

e 11

e 4

c 46

e 8

What is my stock count?

1) I started hard-coding the MiddleWords() lines but I won't know exactly how many lines there will be so I think I need to wrap my calc with the total ValueCount() and end with 999 or something.

2) I need to trap for error (letters other than e or c) using Filter?

3) I will need to enclose it in an If() test, I suppose.

Yep. I should just script it ... end my misery ... but that wouldn't end my desire to understand it. Anyone want to play with it for me and then share your thinking and attack plan? My sanity would be ever-so grateful. I'll have to resort to an ugly scripting process in the morning. I hate horsing things that can be handled with precision and finesse'.

Oh. I know I could force entries into separate lines, yada yada. The point of this is to learn it. I hate problems I can't figure out. I wish I hadn't thought about using a calc - I'd have had the script finished by now. But it's too late - my curiosity over the problem has consumed me.

LaRetta

Inventory.zip

[comment]

Well, you COULD do it like this - but you know very well that you SHOULDN'T.

Inventory.fp7.zip

[LaRetta]

Oh good God! Extend! Geez! I have to study this! Thank you!

When I got the email and it said I SHOULDN'T, darned if I wanted it in the worst way!! Why is that? HAH!

And then Stephen chooses NOW to backup using his ole Pentium 75 or something and it took forever to get on here! Like when you're in a hurry and get stuck behind a school bus!

I'm wide awake now! Had fallen asleep sending my keyboard buffer to screamin' - not sleepy now!

Thank you again! God, this tickles me! I'm terrible at calculations and I totally ADORE them! Go figure!

ps - I tried using Choose() and Extend() but couldn't get anywhere at all. Reps yes! And YES I SHOULD precisely because I shouldn't!!!

Ahh, you even Trimmed it for me.

L

[Ugo DI LUCA]

Hi,

I don't have FM7 here so I can't see what Comment suggested, but here are 2 ways I could think of.

The first need your entry input to be somehow reversed so that you get

10 e

12 e

20 c

and then

Evaluate ( "Sum ( " & Substitute (Input; ["e";""];["c"; " * "& "Products::QtyCase"];[

[Ugo DI LUCA]

What an idiot !

You don't have to reverse anything in your input of course...

Evaluate ( "Sum ( " & Substitute (Input; ["e";""];["c"; "Products::QtyCase" & "* "];[

[Ugo DI LUCA]

) I need to trap for error (letters other than e or c) using Filter?

I didn't see that but going through the evaluation process, which I have the feeling should work, you could use a combination of both to filter only letters and numbers, and separators too within the same calc.

In my opinion, you could substitute "c" by Products::QtyPerCase, "e" by 1 and any other letters by 0.

As a test...

Evaluate ( "Sum ( " & Substitute (Input; ["a";"z"];["b";"z"];["d";"z"];["f";"z"];["g";"z"];["h";"z"];

["i";"z"];["j";"z"];["j";"z"];["k";"z"];["l";"z"];["m";"z"];["n";"z"];["o";"z"];["p";"z"];["q";"z"];["r";"z"];

["s";"z"];["t";"z"];["u";"z"];["v";"z"];["w";"z"];["x";"z"];["y";"z"];

["z";"0"&" * "];["e";"1"&" * "];["c"; "Products::QtyCase" & "* "];[

[LaRetta]

I pasted your calc in, Ugo, and it produces the total - right! It works perfectly, sir!

MiddleValues(), Extend() and Get (CalculationRepetitionNumber) are my new loves.

Give me another 10 years and I'll catch you ALL in understanding calculations ... that is, if you foolishly slow down to look back! I'm so glad I asked and especially glad you both responded. I see many of my (thinking) errors within both your calculations. Comment, your calculations always provide clarity of principle for me which I desperately crave! Understand the principle and the way will solve itself.

Now I can sleep. Must get up in ... ooops, 45 minutes for work.

Who says a girl can't TRULY enjoy an all-nighter.

Ooops! Ugo, I see you posted again before my response - I might have accidently closed my eyes for a moment! You are a lot like me in our slightly-out-of-control enthusiasm! Well, I'm a bit too tired to look at that, ummm, filter. But you can bet I will when I get first opportunity and my eyes will focus. I'm sure it'll work too!

LaRetta

[LaRetta]

I'm so tired, I'm hallucinating who I am.

Ugo, tried to sleep but can't until I tested your final one. Yours works but if I put f it produces a ?

I have no doubt you'd fix that. Now I can sleep. I hope I dream of MiddleValues() - very quickly.

[Ugo DI LUCA]

Post deleted by Ugo DI LUCA

[Ugo DI LUCA]

Ok,

Here's another one...

Let([

X=Substitute (0&"

[Ugo DI LUCA]

Cleaned...

Let([

var ="Products::QtyCase";

InputConv =0 & "

[LaRetta]

Alright, Comment and Ugo, I have come to my senses (what little I possess). I have rolled around in both your calculations like a pig in mud. I've created over 50 demo calcs combining and testing their theories and hopefully have learned a great deal. I am satiated (for the moment). And I have incorporated this into my Learning db. Now for reality ...

Comment said... Well, you COULD do it like this - but you know very well that you SHOULDN'T.

Do I? I doubt that. I have no problem breaking rules and norms (in fact, it tickles me). As far as I'm concerned, there is only one SHOULDN'T in FM ... and that would be to create inefficent, dawg-speed junk. But some things are difficult to speed-test and I don't understand FM's resource/evaluation process in many contexts.

To clarify, my one-file, 46-table, 500MB, peer-to-peer current solution is lightening fast. Users don't wait two seconds for ANYTHING and I plan to keep it that way. I always have two goals: User-friendly and lightening-fast. The User-friendly is achieved (in this instance) by allowing them to enter data in this one text field. I have seen the inventory-count sheets data-entry is presented (chicken scratches). I have sat down and data-entered it myself and know what it feels like and agree this is the easiest for them. Now I must address the second part - speed and resource-efficiency.

What is the most efficient way to handle this text field? Script, Comment's calculation or Ugo's calculation? Data-entry doesn't need to see the total dynamically (although it's nice); only their entry field. Evaluate tends to make me cringe (I've heard it's resource-hungry) and I want to use it prudently. And both calculations must be unstored, correct? This will take place in a small table so it may be moot but every nanosecond counts. I tried to test them in my LineItems file (I use it for all testing because it's huge and results stand out) but can't capture (or notice) the differences.

Speed-wise, efficiency-wise, which method would you use if you were me?

LaRetta

[Ugo DI LUCA]

Hi,

A script would be my bet, involving the evaluate ( ) as you wouldn't be able to replicate the repetition approach within a script without any repetition field in place, and without a loop through them.

Now, the reps let you handle much more details and oddities than the Evaluate ( ).

And the Evaluate( ) itself can be triggered so that it becomes indexable in an auto-entered text field. The repetition approach can't.

[comment]

I am laughing out loud: I should be defending repeating fields against Ugo?

Anyways. The only reason why this cannot be stored is the 'Qty Per Case' field. Make that a lookup, and the problem is gone. Other than that, I don't know.

My SHOULDN'T was aimed at the data entry method. First you mix data together, then you ask how to separate it. The best method, IMHO, is not to mix it in the first place.

That and the fact that there is no trail. Can you check it? If there is an error in entry - can you hunt it down? Take for example:

e 11

e 4

c 46

e 8

I am deducing, Watson, that this product was counted at at least 3 separate locations. Why? Why else would someone enter e 11 and e 4, if they could have counted them together as e 15? No doubt they counted e 11, wrote it down and moved to count the next location. Now, can we go to location x, recount and verify? No we can't, because we don't know which of the lines was counted at location x.

[LaRetta]

Comment said...My SHOULDN'T was aimed at the data entry method. First you mix data together, then you ask how to separate it.

Oh, everything you say is sooooooo ... well, logical. If I was all-powerful we wouldn't need this thread at all because the 'sheets' would be much different (at least column'd by location) and their process would be more, well ... logical and therefore, much easier for data-entry.

That and the fact that there is no trail. Can you check it? If there is a error in entry - can you hunt it down?

No, not at present and a primary argument against their 'sheets' and process. This will be double-entered by two different people for validity of entry (yep, it sucks). I told them if we get these inventory problems corrected, they won't have to re-count the entire inventory every week (something they've had to do for years)! I'm already on the Shipper's sh_t-list for changing their stock order although the Owner said (and Ship Managaer verified) it saves 5 man-hours a day. Shippers have threatened to walk if I mess with their sheets though. They've drawn the line. But I will eventually win (and thus so will they).

I am deducing, Watson, that this product was counted at at least 3 separate locations. Why?

Properly deduced sir and it makes me chuckle. Shippers said the computer (FM solution) miscounted inventory. Owner made them take the invoices and manually check them. Surprise ... 'computer won.' Stock sits in various locations, unprocessed:

- Returns not yet checked in

- Credit card orders which rejected and need to be returned to inventory

- Stock with special sens labels for special-order clients (will be tracked separately soon)

- Different Lot#/ExpDates (yes, they'll be tracked separately soon)

- Blemished product yet undetermined whether sellable or not.

- Stock sitting on shipping table after shipments leave (oh dear, which box did THAT belong in?)

HAH! Their inventory is in the best shape it's ever been (tell you anything)? I'm pushing for a bar scan system and full reorganization because of these issues; in the meantime, our data-entry gal needs help. It's these kinds of wonky situations that force me to reach outside the box for solutions, such as this thread.

And, God, I learned a bunch on this one! Thank you so much.

LaRetta