extract negative values from repeating text field

[pubdss]

I have been trying to figure out how to extract negative values (or values with a "-" in it) from a repeating text field.

i found this script on the FileMaker TechInfo site, which was able to create a text box and input all the values on separate lines - I am not sure if I can use/alter this script for what I need to do.

Basically this repeating text box has positive and negative numbers and I want to create a report that only reflects the negative numbers, and a sum of those numbers at that.

I cannot figure out how to modify this script from the link above, for what I need, or if there is a better way to do it. Is there, maybe, a quick calculation for this field that would be better?

Any help, would be much appreciated!!!

Thanks!!!

[-Queue-]

This isn't too difficult using a few calculations and a value list. See attached for an example.

NegValues.zip

[Ugo DI LUCA]

Attached a small example

HTH

NegativeReps.zip

[Ugo DI LUCA]

Eehhe.!

You here ?

The good thing with yours is that even if entered into "( )", the value is returned as negative.

And it is really converted as num. Was overcomplicated with the Sign( ), which I've been using too much in my files for a few days.

[bruceR]

You can't isolate records with repeating fields. Time to get rid of them and build a proper relational design. There are workarounds but they are a waste of your time.

[-Queue-]

What do you mean by isolate?

[bruceR]

You can't do finds, reports, deletes, etc. If you want to delete row row 3 of a repeat - you can't. If you want to show all repeats where PartNumber= ABC123 from 1st quarter 2004 invoices, you can't. If you want to sort them, you can't. If you want to import row 5 of a repeat into another table, you can't.

[pubdss]

Thank you and Ugo as well. I think I might owe you both my soul now!!

Haha, both worked perfectly, i ended up sticking with the Choose (Sign) method to keep things simpler.

Thanks again!

[-Queue-]

Well you can do finds and deletions, although deletions may require a little tweaking. It's true that reporting can be difficult, but for the example given, it doesn't seem like this will be an issue. It's up to pubdss to determine if that will truly be a problem though.

[-Queue-]

Note that you could change the Choose to a Case that eliminates the need for a "" result. I wouldn't think that it's simpler than the other sample, but I did assume that you wanted a list of only the negative values in its own field. If you don't require that, then you don't even need the value list or ValueListItems calculations, just the Case( TextToNum(text) < 0, text ) calc and the Sum one.

[bruceR]

Using repeats and then having difficulty accomplishing something is like pounding nails in your tires while you're trying to learn how to drive and then spending all your time taking tire patching lessons. It is counterproductive, a waste of time, and prevents you from learning fundamental skills.

[-Queue-]

Learning how to use repeating fields properly is a fundamental skill as well. There's no reason to turn every one off from them. They aren't evil; in fact, they're more powerful than normal fields when you learn how to work with them. Granted there's few reasons to use them for static data, of which the example does not seem to be the case.

[bruceR]

No, it's not a fundamenal skill, and it mostly interferes with learning basic relational concepts. It is very much a secondary or higher level skill and certainly has its place. But not for most people here, who are struggling with relational concepts.

[-Queue-]

I agree to disagree. It need not interfere with learning relational design, nor need it be overly complicated to learn. Very simple repeating designs can have a major impact on the interface and/or development time. Those who are discouraged from ever messing with them can waste much time trying to develop a workaround that can easily be implemented with a calculated repeating field. It would be much better to include them as part of a basic learning package. True they can require a higher skill level for solutions such as those DJ or Ugo develop for ideas no sane individual ever thought possible . But they don't always have to be that difficult, as not every text manipulation solution needs to have dozens of levels of text functions involved for it to be beneficial to the developer.

There is such an unnecessary stigma against repeating fields that doesn't compare to any other feature. You never see "Don't use that function; it's too difficult for you to comprehend!" No, you see explanations of how it works and some examples for usage. The user decides whether it's too difficult or whether they even want to bother learning it. It should be the same way for repeating fields, instead of jumping up and down, screaming 'No!', eventually convincing most that there is no purpose for them and making them less than a tertiary skill level because no one has any experience or clue how and why they work. FMI obviously considers them more than a holdover from the past and has increased their potential with version 7. Why not take advantage of this boon, rather than treating them as taboo and on par with a grave cardinal sin?

[DykstrL]

I agree with -Queue-. As with all features and functionalities in FileMaker, each has it's place and time.

I have used repeating fields since v1.0 days and they definitely are VERY useful. If you don't need complicated calculations or need statistical analyses, and can determine a finite number of repitions, there is nothing wrong with using them.

It's just another tool in your toolbox.

[bruceR]

They aren't taboo, for me. I'm quite adept at them, And nobody - at least not me - is screaming anything, certainly not no don't ever use them.

They are, almost always, the wrong choice, especially for novices, and they do in fact prevent people from learning basic relational design as can been seen by looking at about 90% of the questions here about repeating fields including this one.

Can the people asking these questions build a portal, build a filtered portal, build a list view, perform manual and scripted finds, build sub-summary reports? That is the first order of business with a relational database.

None of the other feature you mentioned are argued against in a comparable way because none of the other features defeat relational design.

[-Queue-]

Well, you often appear to be 'screaming', not to mention quite volatile and angry. Perhaps I and several others have misinterpreted your 'tone', which, I'll admit, is easy to do on here.

Anyway, just because novices are confused about the purpose of repeating fields doesn't mean they shouldn't learn about them. I would think the opposite. They should be introduced to them and their usefulness but cautioned regarding their limitations and when it is more appropriate for a relational design to be implemented. I don't believe they prevent anyone from learning relational concepts. If there is no one to introduce new users to a relational understanding, then, of course, they will probably try to take what seems the easy way out. But if they know no better, then I wouldn't say it's their fault for being ignorant and would help them try to understand both processes, along with the pros and cons involved with each. I think there is a more subtle and encouraging way of doing this than jumping up and down, waving one's arms, 'screaming' about the dead end toward which they may be traveling, which it seems many of your previous posts do. I tend to think such responses have more of a negative effect than a positive one.

I don't believe that repeating fields defeat relational design. I think they have a great capacity for enhancing it. It all depends on how one uses what one knows.

All in all, I think I've had a bigger issue with how your perspective has been expressed than the perspective itself. If I were a newbie reading any of your seemingly anti-repetition posts, I feel I would be turned off from them completely. Years ago I encountered a similar vehemency against the feature and was encouraged to never even consider or pay attention to it. I myself answered posts on other forums with a strongly negative attitude against it. Eventually I began experimenting with them, just for the heck of it, and was astounded what could be accomplished and what could be simplified by adding them. I realized I could have saved a great amount of time and streamlined processes, not to mention eliminated dozens to hundreds of superfluous fields, if only I had learned about them when I started developing with FileMaker. If I had not taken a guru's advisement to heart as a novice, I could have saved so much development time during the intervening years. That is why I have become such a proponent of repeating fields and of any skill level getting at least a glimpse of their power. I don't think anyone is too ignorant to learn the differences between relations and repetitions or that they should be heavily discouraged from incorporating or experimenting with both in a solution and determining which technique is right for the particular job.

[bruceR]

Yes, perception is fascinating isn't it. You could read my posts as persistent and informed and helpful if that's how you wanted to see it.