Need a calculation

[AudioFreak]

I have a field that is populated with numbers and letters. EX: R1,R2,R3,R4

The current calc I'm using is a word count. From the example above it gives me 4.

Is it possible to enter the numbers this way EX: R1-R4 And still get the same result? Sometimes the field is populated with up to 150 different numbers. Any help is appreciated. I'm using FMP6 if it matters.

Michael

[Jason Goldsmith]

Does your field always contain a sequential span of numbers, such as R1-R25, or R1-R150? If so, the attached should work

Trial.fp5.zip

[-Queue-]

RightWords( Substitute( Field, "R", "" ), 1 ) - LeftWords( Substitute( Field, "R", "" ), 1 ) + 1

should work for Rx-Ry, where x >= 1 and y > x.

[Ugo DI LUCA]

From the example you gave, it's not clear what result you're expecting.

Is it the number of occurences or the difference from the last number and the first number ?

With the French version, there was no difference between a worcount using "," and "-". Is it that the US version doesn't behave that way...

[-Queue-]

Actually, Ugo, "R1-R4" works the same as "R1 - R4", "R1- R4", and "R1 -R4", with my calc.

[Ugo DI LUCA]

Not with the French Version....

I guess no problem if you never cross the Atlantic with this calc.

I've pointed these differences lately, which was even worse using the TimeToText function.

[-Queue-]

Along those lines, I was thinking we need a forum for system settings issues. Then we could have a place to look up which calcs don't perform as expected in different regions and how to modify them for universal use.

[AudioFreak]

I'll have to check out the sample provided tonight when I get home(If I get Home) They are closing a lot of roads due to the fires. What I'm trying to do is make the input of the numbers easier. This is a count of a specific part for a BOM. Lets say there are 4 10K resistors on this board. They are located on the board at R1,R2,R3,R4. Instead of typing all those individually it would be much nicer to just type R1-R4 meaning that R1 through R4 are 10K resistors and there is a total of 4 on this board. Sometimes the field may look like this.

R1-R4,R10-R15,R19,R35 which is a total of 12. This may be really tough to pull off, at least for me. But guys like Ugo and Queue are calc wizards and always have a way of doing tings like this. Thanks for the help.

Michael

[Ugo DI LUCA]

Hmm...

Calc wizard or not, you'd better script this one. Scrowl down for the script process.

Now, may be calc....

You said you could have up to 150 numbers here, but it also depends upon the number of "," , which in your example seems to be a logical separator.

((4-1)+1) [color:"red"] + ((15-10)+1) [color:"red"] + ((19-19)+1) [color:"red"] + (35-35)+1.....

Depending on the number of red "+" , you could use a calc along the lines of :

(RightWords( Substitute(Substitute(MiddleWords(Substitute(Substitute(Substitute(text," ",""),",","

[AudioFreak]

Hi Ugo,

If I went with the first option, I would say " Thank God for copy/paste".

I'm going to agree that scripting this is much better. I will give this a shot later and let you know how everything goes.

Thanks, Michael

[AudioFreak]

Looking at the calc and noticing all the "R" in it. This may cause a small problem. In the same field on other records the letter may also be "C" for capacitor, "D" for diode..etc. Will this calc still perform ok? It appears that it probably will not.

Michael

[Ugo DI LUCA]

This was one of my concern in my former answer...

As you didn't replied on it, I thought it was OK.

So now, you could *try* the following :

TextToNum(RightWords(Substitute(MiddleWords(Substitute(Substitute(Substitute(text," ",""),",","

[Ugo DI LUCA]

-Queue- said:

Along those lines, I was thinking we need a forum for system settings issues. Then we could have a place to look up which calcs don't perform as expected in different regions and how to modify them for universal use.

JT,

Wouldn't you agree these kinds of issues should be taken into account by FMI itself ?

They are the one that should sell a "Universal" product.

Now obviously, as these Forums are a crossroad of different regional users and developers, it could be that some dedicated place on the Forum might help.

[djgogi]

This is a perfect example for the use of an iterator (basically repeated field with sequential numbers in it's repetitions).

Check the attachment

Dj

AudioFreak.fp5.zip

[-Queue-]

Gah! You finished it before I had the chance. Oh well, nice job! I was working on this last night, had a repeating field involved, but couldn't quite >squeeze< it the way I wanted. Now I'll be memorizing your demo for future reference.

I guess I failed living up to the 'Calc Wizard' nomenclature. DJ now holds that title.

[Lee Smith]

Hi Dj,

Wow. Just when I think I have began to figure this stuff out, wham along comes a different approach.

Would it be possible for you to explain your file a little bit, for those of us that are not familiar with this process?

TIA

Lee

[djgogi]

Hi Queue, Lee

well i have already posted few examples here that implement the same technique.

Namely MathWithText in sample forum and Concatenating Repeated Fields in Define Fields forum.

So, to explain it a little bit...

Imagine what is happening inside an script that perform some repetitive action inside a loop.

First we init the counter to it's starting value ( 1 for ex)

Than we perform action using the counter value

Next we increment the counter and then we perform the same action again using the new value of counter.

Finally we exit the loop when some condition is fulfilled.

To emulate the same thing inside an calculation (without involving the scripts) we need to find some way to deploy simultaneously different (all) values for the counter on same formula in same place.

And that's when iterator comes in.

As I said iterator is an repeated (global) numeric field that holds all values for the counter (they don't have to be consecutive, however)

With the limitation of 1000 values (max number of repetitions for an repeated field)

Anyway this limit could be eliminated thru use of additional iterators.

The rest is simple, by applying the Extend function to our source field together with our iterator we can perform some very interesting calcs that otherwise would require looping script.

Dj

[-Queue-]

I knew it was possible, and I stayed up fairly late to try to prove that it wasn't as difficult as Ugo's sample. But I had no idea it would be so elegant. Thanks again!

[AudioFreak]

Well I have this sort of working. It calcs up to the first comma but no further. Ex: R1-R26,R35 The calc returns 26.

I understand the calc needs to be duplicated for each comma(is this correct) but when I try to create the second by copy/paste I get the error "an operator is expected here" Here is what I'm *trying* to use.

TextToNum(RightWords(Substitute(MiddleWords(Substitute(Substitute(Substitute(D#," ",""),",","

[Lee Smith]

Hi Dj,

Thanks for responding.

I will check out the other files mentioned and study them.

Lee

[Ugo DI LUCA]

Goran, this is a neat solution for sure.

I'd have to explore further the method, but I've already found some implementation of this technique...

AudioFreak, let me explain a little the calc I posted too. Understanding it would help using it.

It's an adaptation of one solution Ray posted once about extracting value from a list using the Middlewords().

The challenge with this function is to take care of "spaces", as each introduces "space" would be considered as a separator.

The Goal is to turn a calc of type R1-R5,R9, R10-R15,R35 into :

R1-R5

R9

R10-R15

R35

In order to parse it easily afterwards

This technique use nested Substitute().

1. Substitute(D#," ","") would eliminate any trailing space

2. Substitute(D#,",","

[Lee Smith]

Hi Dj,

I found both referenced files and downloaded them, however I did have trouble finding the second file. Come to find out, the referenced discussion subject should have read:

Concatenate Repeating Fields

Now all I have to do is figure them out.

[AudioFreak]

Ugo,

Thanks so much for the complete explination of the calc. Having a better understanding of what it is doing is very helpful. I did figure out the missing + was the problem and was closing in on the String1+String2+String3 idea. I have read your post multiple times and am getting a better idea of what it is actually doing. Thanks for the help. Maybe some day I will be as good at calcs as you and some of the others here on this forum.

Michael

[AudioFreak]

Well I'm not sure if I've done something wrong but when the calc is duped to get the individual strings it still calcs as 1 even if a string is not present. If a srting is present the calc works fine. I took a deeper look at the sample provided by DJ and must say that is pretty cool. At first I was unsure because I'm not fond of repeating fields. But this is a very different and very cool! Not the repeating fields I'm custom to.

Thanks for the sample DJ!

Michael

[Ugo DI LUCA]

I don't get it David,

Is it that when the global is empty, it still takes the last string. As I said, 0 or 1 used as the "occurance" in the Middlewords() would bring the same value onto the stack.

That's why you should set the counter in the loop script starting at 1, and then up, and why you should stop the loop before you reached the last "word".

[AudioFreak]

Not sure who David is but I did manage to get both Ugo's and DJ's solution working. Thanks for all the help and suggestions.

Michael

[Ugo DI LUCA]

Ooops...

Sorry Daniel

My apologizes, don't know this guy neither...

Glad you fixed it anyway.