gaby78 Posted May 19, 2006 Posted May 19, 2006 I have a 3 fields: StartDate (user input), EndDate (user input) and Dates (calc?). What formula or script do I need in Dates field to break down the range into dates separated by carriage return. For expl if StartDate is March 19, EndDate is March 23, I want in Dates field: March 19 March 20 March 21 March 22 March 23 Thanks in advance
LaRetta Posted May 19, 2006 Posted May 19, 2006 Actually Ed, with vs. 7/8, multikey dates aren't as necessary (for all cases). For example: StartDate (user input) should be global. EndDate (user input) should be global. Relationship from this main table to your related (or self table). You would then use something like: Maintable::gStartDate :less: Related::dateField AND Maintable::gEndDate :great: Related::dateField The relationship doesn't have to be User input ... it can be global calculation or unstored calculation as well. Does this help? If you have something more specific, we can help you through it. LaRetta :wink2:
gaby78 Posted May 19, 2006 Author Posted May 19, 2006 Thanks LaRetta for trying to help. Here is the situation: I have a Technicians table with TechID, Name, etc..., and Assignment table. The Assignment table (linked to Tech table) has TechID (drop down list to choose a technician), StartDate, EndDate, Dates, etc... My main concerne is to avoid double booking. I figured that by listing the dates a technician is booked in a Dates field and "subtracting" the next 30 days from a technician's booked days would leave the days he is available. I don't know how else to approach the problem. Your help will be highly appeciated.
LaRetta Posted May 19, 2006 Posted May 19, 2006 Ed, the relationship suggested should work then - just switch it, ie, your bookings would contain a FromDate and ToDate. This range would relate to a calculation with the span as you've indicated. There are three ways (that I'm aware of) to produce the span: a Custom Function (but you don't have FMA (Advanced); a static key calc (in which the User still selects their desired 'range') and the calculation produces the multikey; or via script to set a multikey. Multikey would look something like: gStartDate & ¶ & gStartDate + 1 & ¶ & gStartDate + 2 & ¶ & ... etc... I am not qualified to suggest the best approach here. There is an excellent demo of a booking system on Ray Cologon's website at NightWing Enterprises. However, it uses a custom function. vs. 8 offers more options, ie, GetNthRecord. But a non-equijoin might also work well for the parent key, ie, the User inputs a StartDate into global date field. EndDate could be global (date) calculation = Date ( Month ( gStartDate ) + 1 ; 0 ; Year (gStartDate) ) ... giving you a 'month' span. You would also include the TechID of course in the join (on =). Well, take a look at the demo. And others may have suggestions as well. Some even recommend a record for each date, ie, you enter the Start and End booking dates, and script creates the records. But I shy from needless records. Do you have a sense which might work best for you? If so, we can help you structure it. There are many great people on FM Forum and ideas abound. L
comment Posted May 19, 2006 Posted May 19, 2006 There's no need for a multi-key on either side. The relationship can be from range to range. In this case, it would be a self-join of the Assignments table. IIRC, the relationship criteria need to be: Assignment::TechID = ConflictAssignment::TechID AND Assignment::StartDate ≤ ConflictAssignment::EndDate AND Assignment::EndDate ≥ ConflictAssignment::StartDate AND Assignment::AssignmentID ≠ ConflictAssignment::AssignmentID The last criterion exludes an assignment from relating to itself, so if there are any related records, there's a conflict.
Søren Dyhr Posted May 19, 2006 Posted May 19, 2006 Actually Ed, with vs. 7/8, multikey dates aren't as necessary (for all cases). However - did LeCates show us at devcon, that FM7 uses a lame algorithm for theta joins, compared to the later FM8 ...this is why this statement chimed in back in 2004: I've found using range calcs like these to be quite a bit faster than using theta joins within FileMaker- especially when there are multiple relationship predicates. I imagine this kind of thing will be in our tool kit for a while. ...snipped from: http://www.clevelandconsulting.com/support/viewtopic.php?t=299&highlight=smartranges The hashing algorithm this thread deals with, is this: http://www.onegasoft.com/tools/smartranges/index.shtml --sd
Recommended Posts
This topic is 6825 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now