Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Range of values


clivemas
 Share

Recommended Posts

If I have a Start field and End field with values like Start: 1157, End: 1162. What formula should I have in a 3rd field (Slip Numbers) to return: 1157, 1158, 1159, 1160, 1161, 1162. I want to break down a range into individual values separed by comma or carriage return.

Thanks.

Link to comment
Share on other sites

If I have a Start field and End field with values like Start: 1157, End: 1162. What formula should I have in a 3rd field (Slip Numbers) to return: 1157, 1158, 1159, 1160, 1161, 1162. I want to break down a range into individual values separed by comma or carriage return.

Thanks.

Whilst there are several ways to approach the problem you've outlined, one that you might like to consider would be the use of a Custom Function. For instance, if you use FileMaker Developer (or Advanced) to define the following custom function:

 

NumRange ( From ; To )

-------------

If(

From and To > From;

From & ", " & NumRange ( From + 1; To );

From

)

 

You will be able to use it to generate ranges of up to 10,000 integers in the format you require (ie a comma-delimited list). smiley_cool

Link to comment
Share on other sites

Hi CobaltSky:

Thanks for your answer. Unfortunately I am using FMPro 7.03 and don't have the NumRange function; I suppose it's new in FM8. Is there a different approach? Thanks

Link to comment
Share on other sites

Hi CobaltSky:

Thanks for your answer. Unfortunately I am using FMPro 7.03 and don't have the NumRange function; I suppose it's new in FM8. Is there a different approach? Thanks

NumRange( ) is a Custom Function which will work in FileMaker 7.0v3.

 

As per my previous post, Custom Functions must first be installed using FileMaker Developer 7.0 before they will appear in calculation dialog lists. What I provided above is the definition for the Custom Function.

 

If you don't have a copy of FileMaker Developer and can't get access to one, then there are some other options, but the Custom Function approach is preferable. smiley-wink

Link to comment
Share on other sites

Unfortunately I don't have FM Developer. What other options do I have?

Thanks for your help.

Well, like I said, your best option would be to beg, borrow or *buy* the Developer/Advanced version. You're trying to do advancved stuff without the right tools.

 

However, I did say there are other options and there are - but they have their drawbacks.

 

The second suggestion I'd make is that you provide a script to create the values and places them into a field. It has the advantage of being open ended (no range too small or large) but the disadvantage of not updating unless the user clicks on an 'update' button, or unless you use a plug-in to trigger the script.

 

I am attaching an example. smiley-smile

Link to comment
Share on other sites

pasted in by kjoe From the disappeared post by clivemas

It will be used as follows: An agent is assigned Tickets # 1157 to 1162. When he comes back a week or so later and I enter the ticket # left like 1158, 1160, 1161; by "substraction" I get the tickets sold #1157, 1159, 1162

 

 

CobaltSky

I can't dowload your file; it requires paid subscription.

 

 

 

I am not sure how you envision this "subtraction" part. It's another recursive calculation that would require a custom function or a looping script - a more complex one this time.

 

I think I would just create records in a related table, one record per ticket. This would be scripted. Then you can mark the tickets left (or the tickets sold). Or enter the numbers, separated by a carriage return, into a field and use this to filter a relationship to the tickets table.

Link to comment
Share on other sites

If I have a Start field and End field with values like Start: 1157, End: 1162. What formula should I have in a 3rd field (Slip Numbers) to return: 1157, 1158, 1159, 1160, 1161, 1162. I want to break down a range into individual values separed by comma or carriage return.

Thanks.

 

Set var $temp Value: num_Start

Set Var $txt_result Value: getastext $temp )

loop

Set var $temp Value $temp + 1

Set var $txt_result Value: $txt_result & ", " & getastext $temp )

Exit Loop if ( $temp => num_END)

End Loop

 

Oh, You are using v7...

 

Set Field gv_num_temp Value: num_Start

Set Field gv_txt_result Value: getastext gv_num_temp )

loop

Set var gv_num_temp Value gv_num_temp + 1

Set var gv_txt_result Value: gv_txt_result & ", " & getastext gv_num_temp )

Exit Loop if ( gv_num_temp => num_END)

End Loop

 

gv_num_temp and gv_txt_result are global fields.

 

That is off the top of my head, it may not be work perfectly, but it should be pretty close.

Link to comment
Share on other sites

Hi samantha:

Thanks kindly for the script. At least it is a good start. It's going to take me a few days to decipher it but I'm sure it will lead me in right drection.

Thanks again.

Link to comment
Share on other sites

Hi samantha:

Thanks kindly for the script. At least it is a good start. It's going to take me a few days to decipher it but I'm sure it will lead me in right drection.

Thanks again.

 

I had a sec, here is a working example. :D

Link to comment
Share on other sites

I had a sec, here is a working example. :D

Hmmn...

 

Looks like you're wasting your time here, alajjana samantha. I had already posted a file with a working example of a script a couple of days previously (see post #6). But clivemas responded to say:

 

CobaltSky

I can't dowload your file; it requires paid subscription.

 

After all, as per his profile, he *is* an accountant... Oh Really!

 

BTW it seems that the post which contained this deep insight into clivemas' value system has disappeared "into the ether". My guess is that it got overlooked/deleted/munged when kjoe edited the thread to extract the discussion about tail recursion... Never Mind!

Link to comment
Share on other sites

hey that's strange. I'm pretty sure all I did was move some posts. and I cannot hard delete posts anyway. To repair things, I pasted the original post as a quote into comment's reply (post #8). lucky I had not deleted the email notification. hope this fixes things satisfactorily.

 

kjoe

Link to comment
Share on other sites

"Samantha" is fine. :) or just "Sam"

 

As much as i would like helping someone else I also need the pratice. I so rarely get a chance to sit and work on my own project, I will take any chance to work on someone elses. smiley-tongue-out

 

I glanced at the recursion thread, pretty interesting stuff. I was planning on taking a closer look at it when i got some time.

Link to comment
Share on other sites

"Samantha" is fine. :) or just "Sam"

 

As much as i would like helping someone else I also need the pratice. I so rarely get a chance to sit and work on my own project, I will take any chance to work on someone elses. smiley-tongue-out

 

I glanced at the recursion thread, pretty interesting stuff. I was planning on taking a closer look at it when i got some time.

No problem, Sam.

 

As regards kjoe's recursion thread, it deals with a very particular and somewhat obscure variant of recursion (ie tail recursion) - which may be a tough place to "cut your teeth" if you're new to recursion and/or to custom functions in general... ;)

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use