Jump to content
Sign in to follow this  
rothdavid

Recursive Date List Function

Recommended Posts

I have a project where I need to make a value list of dates based on start date and end date.  Once I have a value list I can then select a value position based on other criteria.

 

a date range on the record may be:  (start) 1/1/2014  to  4/15/2014 (end)

 

The list created would always be for the first day of each month for the duration of the date range:

 

1/1/2014

2/1/2014

3/1/2014

4/1/2014

 

I'd like this to occur as a calc or CF, I'd rather not script this to create a variable because there may be a few hundred needed to generate.

 

Thanks,

 

David

 

Share this post


Link to post
Share on other sites

I would use a repeating calculation field for this =

Let ( [
start = Extend ( StartDate ) ;
d = Date ( Month ( start ) + Get ( CalculationRepetitionNumber ) - 1 ; 1 ; Year ( start ) )
] ;
Case ( d ≤ Extend ( EndDate ) ; d )
)

The advantage being that the result type is Date. The (minor) disadvantage is that you must pre-set the number of repetitions to the maximum expected duration in months.

 

Once you have this in place, create a self-join relationship matching on the record's unique id and define the value list to use the related values.

  • Like 1

Share this post


Link to post
Share on other sites

So you use the related values based on id to constrict the dates otherwise a regular value list would show dates for all records.  Is this correct, Comment?  


Never mind. I should just test it.  sorry to bother.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

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.

Sign in to follow this  

  • Similar Content

    • By mvoogt
      I have started sending emails using the INSERT FROM URL method shown by soliant here:
      https://www.soliantconsulting.com/blog/html-email-filemaker/
      Emails are being sent out but many recipients seem to be getting my emails sent to their spam folders. I'm curious if this has anything to do with this method of sending emails.
    • By Stickybeak
      I have been running a document creation and management solution for 2 years.
      My scribe maintenance subscription ran out so I bought a new license.
      Boom the whole things has collapsed.
      Scribe is throwing errors when a field in the database does not appear as a merge field in the document the attached being a typical error.
      This solution is vital to my practice but 360Work have no taken 2 working days with no solution - not responding to my emails when I tell them that their work arounds aren't working.
      This was suggested "ScribeSetErrorCapture(true)" - but that does not even appear in my steps in the script window.
      Utterly frustrated.
      Help!

    • By wedgeman
      Not sure if this is the best neighborhood for the question, but it is tied to network/db security.
      What method is used to best determine how a user is logged in? Is Get(MultiUserState) the best method for determination of who/how someone is accessing a locally shared (peer-shared) database?
      For licensing purposes, we run several authorization scripts during login, and it is helpful to know how the access is working.
    • By wedgeman
      Am a bit lost on what (I think) should be a quite simple script:
      Field 1 has a value list on layout.
      Field 2 is a general text field for typed data entry..
      My goal is to use Field 1 to trigger/manage a script which does severla things, but then leaves the mouse/selector inserted into Field 2, for ease of use...
       
      So...
      When I select an item in that value list, an OnObjectExit triggered script: 
      sets a variable from that, then clears that field inserts a calculated value (based on the variable created) in another field.  Then it SHOULD "go to Field 2"... as in, after the script is done, the cursor should be inserted into Field 2 with a blinking cursor... My problem is, this isn't working.
      the first two parts are working flawlessly - it creates and acts on variables, as it should..
      But it refuses to 'go to field' (or "go to object", if i try it that way)..
      What's happening instead is this: the script runs, but the selector/mouse jumps on forward to the next object (if that object is in a tab order), or (if NOT in a tab order), it goes nowhere (it operates the script, but dies before setting mouse to the field or object).....
      I also tried OnObjectModify and OnObjectSave.... No dice on either.....The mouse simply refuses to "go to field"....
      additionally, i tried triggering ANOTHER script (which works on its own) - - simply "go to field 2".. but even that doesn't work..
      thoughts?
       
      My guess is that the script (as an OnObjectExit trigger) runs, then FMP assumes that after the script ends, the next object should be selected (rather than ending wherever I told it to end)...
       
      anybody?
    • By 123
      Hey,
      I have a Tab-Separated Text File which I want to import to a database that I just created in FileMaker Pro 15. The text file essentially is a log of events, for instance, one line describes an order, another line describes a payment that has been made. The problem is that I somehow need to import all these lines into different tables. I want all lines in the log file that describe a payment in a table called Payments, and all lines that describe an order in a table called Orders for example.
      I'm just starting out with FileMaker so forgive me if there is an obvious solution that I'm not aware of. I have a background in software development, so the only solution that I can think of is writing a small program myself that does all the parsing for me and creates separate text files that contain all the orders and payments and other events that have been logged in the file. Then I could simply import each file into the table I need all the data in. 
      So to sum everything up, my main question is if there is a way to somehow parse the text file before importing it. I know that you can create custom scripts in FileMaker but I'm not sure if what I need to do can be done just with that.
      Thanks in advance,
      Mike
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.