ranged number data

[insomaniac]

Hi!

I want to be able to find a specific number in a number field that can have a range of number. For example, if the value of that field for a record is "420...700", or "500...501", or "500" , I want to be able to find that record by performing a search with the value "500".

I thought that by just entering the data with the dots (...) it would work, but it just returns the records with the specific value I search. Is there another way I should enter my data, or perform my find?

[Reed]

The dots are for use in find mode to find records with values in that range. If you enter the range in browse mode, then it's just text. Also if it's a number field, then the dots don't work.

[insomaniac]

Thank you for the info. Do you have any idea what I should do?

[-Queue-]

You should be able to do range finds for number fields with dots. I just did one with no problems. I'm not sure what your issue is.

[insomaniac]

Queue, i think what you are talking about is, for example, doing a search for "400...700" and it finds the records containing values like "500". I'm trying to do the opposite...

[-Queue-]

Create a calculation based on your ranges, like Case( num >=400 and num <=700, 500, etc. ) Then do finds on the calculated field.

[Lee Smith]

insomaniac,

Another way would be to do your find 400...700 with the Omit button checked. This will find everything between the number (400...700) and the omit them, leaving you what you want.

Lee

[-Queue-]

I think we have three different ideas of what's being asked here. More clarification is needed. Mine and Lee's ideas don't even relate.

[Lee Smith]

Hi JT,

I think I missed read this.

insomaniac said:

Queue, i think what you are talking about is, for example, doing a search for "400...700" and it finds the records containing values like "500". I'm trying to do the opposite...

I read it to say, I want to find the opposite of a range of 400...700, upon further review this statement makes no sense at all, when you add the part about "500".

I agree with you, we need more information on what is needed.

Lee

[The Shadow]

I think I get what you're trying to do. Does this example do it?

I have the numRange field as a text field, and the beginRange and endRange numbers are parsed from it. To locate a number in the range, type it into the global and hit the "Find It" button, which scripts a find of "<=searchFor" for beginRange, and ">=searchFor" for endRange.

[insomaniac]

Hi people! I guess I wasn't very clear...

Here is more info. I have to do the inventory of all the optical components for a company. Some components can only be used with a specific wavelength (like 515.5 nm) and some can be used for a range of wavelength, for example, from 400 nm to 700 nm. Another example is a component that will work for all wavelenght that are larger than 500 nm.

Now, let's say I want to find a component that will work for 514.5 nm, I want to be able to type in "514.5" and all the records containing the values I cited earlier in my examples should be found.

Another thing that you maybe need to know is that my field is repeating (because some components only show certain properties at certain wavelenght...anyways)

I hope that clarifies what I need to do.

Thank you for your patience and your desire to help me!

[insomaniac]

Those are some solutions that didn't work...

I thought of just doing one field for the maxium value and one for the minimum value, but it doesn't look very elegent since the majority of the components works with one specific wavelenght. I also tried entering my data like this "400 - 700" and searching with "<= 514.5 >=514.5". But it didn't work. Then I tried to do the same thing with different kinds of fields. It only works for non-reaping fields, and for some reasons I ignore, it only works if that field is a TEXT field.

[Ugo DI LUCA]

Hi,

Yes it helps understand your logic, but we need to know more about your repeating field, because "anyways" doesn't help much.

[Lee Smith]

IMO, you are talking about a relationship.

Because I haven't seen your files, I'm "assuming" some things here that may not be true.

If the "nm" numbers appear in "One" field in all of the records for the components it can be used with, then

Create a global field of the same type as the "nm" number field

Create a relationship based on the global field and the "nm" number field.

Left side is the global field.

Right side is the "nm" numbers field.

Place a portal on your layout with a few rows (1, 2, 5, 10, whatever you want to see at the same time.

Now, when you type in 515.5 in your global field, it should show all of the records for every component that 515.5 works with. I'd make a button, and put it in the portal to use the Go to Related Record Script Step.

Lee

[insomaniac]

Guess I still wasn't clear. I'll try again, with an example.

- component 1 works for wavelength >= 400

">=400" will be entered in the wavelenght field (in browse mode).

- component 2 works only with the wavelength 514.5

"514.5" will be entered in the wavelenght field.

- component 3 works with values between 400 and 700.

"400 - 700" will be entered in the wavelenght field.

There is an infinit number possible ranges.

When I perform a search for "514.5" all three components have to be found.

I found a solution I just don't know how to program it.

I'll make two new fields, that won't appear on the interface, MAX and MIN.

Now,

- When only one value is entered in the wavelenght field, it will be copied in both MAX and MIN fields.

- When ">=X" is entered X will be copied in MIN

- When "<=Y" is entered Y will be copied in MAX

- When "X - Y" is entered, X will be copied in MIN and Y in MAX

Is there any way to program that? How do I make it scan through the characters until it finds a specific character?

Now, when a search is performed for "Z" in the wavelenght field, it has to look not in that field but in MIN and MAX, like this <=Z in MIN and >=Z in MAX.

Can anybody help me?

[insomaniac]

- When ">=X" is entered X will be copied in MIN

and a super huge number will be entered in MAX

- When "<=Y" is entered Y will be copied in MAX

and 0 will be entered in MIN

[Reed]

Here's a possible solution based on what you just posted. MIN and MAX are calc. fields using the case() statement. If the entry field contains the text you specified, the MIN and MAX results will correctly display. I chose 1,000,000 for the high wavelength, assuming that's far enough outside of the UV...

This could be refined a bit to catch input that is not valid...(like no numbers in the entry field etc.) but this should work

ranges.fp5.zip