validation question

[egulk2000]

hey everyone,

 

i am having trouble writting this validation calculation, if anyone could help i would greatly appreciate it..

 

i have 2 tables, and for arguments sake lets say its "A" and "B" and i have a joining line table called "line" with some relevant information pertaining to A

 

table A has a field called "Event Date", what i would like to write is that if a record from B is associated to A on a particular date, than i cant create a new record in A with the same date to that record from B, but rather i will need to associate a different record from B..

 

is there any way to do something like this from within a portal / line table??

[egulk2000]

here is an actual example of what i am trying to do..

 

i have a database which tracks events for a band. in each event there can be many band members. what i am trying to accomplish is to restrict a band member from being in two different events on the same night..

[Ender]

I don't believe a field validation will work here.

 

Instead, try a scripted process to create Band-Event join records. Your script can check for the presence of a Band-Event record for the same night prior to adding a new record.

 

Or you could take a passive approach, and show a flag in the portal for items that are overlapping. This would require a relationship between join records, matching on Band member and Date/Time.

[egulk2000]

thats a pretty good idea to create band-event joins, but what would i do if i decided to integrate end times, that the end-user should not be able to add a band member if he is already on a job that day and his job is ending after the start of the current event. but allow him to add the band member if the start time is after the previous end time?

 

and in regards to the script, if its in a portal, what could i use to automatically run the script whenever the user adds a new band member? i have it set now to add someone through a drop-down list in the next line of the portal...

 

also, when you say flag, do you mean conditional formatting?

[Ender]

It sounds like there are two issues to work out. One being showing time ranges that overlap, and the second dealing with Band Members instead of the whole band.

 

Overlapping time ranges are pretty easy to handle once you know the method. Use a range-to-range relationship, with either the timestamp or the time (with date). This means the relationship will use an inequality sign instead of the equal sign when matching the times, like this:

 

Booking Booking 2 =

Booking::BandMemberID = Booking 2::BandMemberID

AND Booking::Date = Booking 2::Date

AND Booking::TimeStart ≤ Booking 2::TimeEnd

AND Booking::TimeEnd ≥ Booking 2::TimeStart

 

 

Your second issue, dealing with Band Members, is more complicated. I believe you will need to generate BandMember_Event join records, and use that as the basis for the relationship above. Populating that will likely be a scripted process, creating records based on the Band's assigned events.

 

The flags I had in mind were calculations based in the join table. It may be possible to use conditional formatting, but I haven't examined it.

[egulk2000]

thank you for your help, i really appreciate it!!

 

i've been bogged down by some other database problems so i didnt really have the time to deal with this more complicated problem, (which at first seemed a little out of my league) but i would like to try to tackle it and give it a try now..

 

can you please explain to me what types of flags you would use?

 

because the truth is i would like to be able to see all the band members and if an error occurs because someone is booked elsewhere, i would like the ability to override it, ( but i wanted to at least be warned about it, which i had thought a custom validation would accomplish )

 

the reason is because if there are two jobs on a given date, one in the morning and one at night, i can sometimes take a particular member off the morning job early and move him to the night one, even though the morning one hasn't ended yet.

 

but if i understand your suggestion correctly ( which i must say is pretty ingenious ) the list that would display, will not include the members at all if they are booked elsewhere...

[Ender]

Well, the Booking to Booking 2 relationship described above would only be used for the flag calc to see if there's an overlap for that member.

 

One correction to the relationship: since it's a self-join, the criteria should also exclude the record you're on. This is done with a ≠ on the recordID:

 

Booking Booking 2 =

Booking::BandMemberID = Booking 2::BandMemberID

AND Booking::BookingID ≠ Booking 2::BookingID

AND Booking::Date = Booking 2::Date

AND Booking::TimeStart ≤ Booking 2::TimeEnd

AND Booking::TimeEnd ≥ Booking 2::TimeStart

 

The flag calc itself then is very simple:

 

Case(not isempty(Booking 2::BookingID); "Overlap")

 

with that calc based in the Booking TO.

[egulk2000]

thank you for all your help. and thank you for sticking it out with me...

 

for some reason i cant seem to get this to work..

 

and i am wondering if the reason is that i am self-joining my line table which connects the band members to the event?

 

i created an example of what i am doing to play with so i dont screw up my DB. would you mind taking a look at it and let me know what i am doing wrong?

 

[ATTACH]1392[/ATTACH]

 

also, how would i integrate the band member to event join?

[Ender]

Two things:

 

1. The ID field that you're excluding in the relationship should be a primary key based in the join table (you'll need to add one).

2. You'll need to populate the Event Date field (try a Relookup on the EventID).

[egulk2000]

You're a genius!!!

 

thanks a-ton!!!

 

and if you dont mind. i am having a problem with a calculation to format a time field. i made (with help from the forums, of course) a custom function to re-enter the field information into a time format because i thought it was annoying to have to put the colon in...

the problem i am having is that if i put in 1200am it returns 12:00 PM, do you have any idea why?

i tried playing with it by adding in the aam and ppm, but no such luck...

 

here it is...

 

Let (

[

string = Filter ( number ; "0123456789" ) ;

am = PatternCount ( number ; "a" ) ;

pm = PatternCount ( number ; "p" ) ;

aam = Filter ( number ; "12am" );

ppm = Filter ( number ; "12pm" );

digits = Length ( string ) ;

error = not ( digits = 3 or digits = 4 or digits = 6 )

] ;

Case

( not error ;

Choose

( digits ; "" ; "" ; "" ;

Time (

Case(

am ; Left (string ; 1 ) ; pm; Left ( string ; 1 ) + 12 ; Left ( string ; 1 )) ; Right ( string ;2 ) ; 0) ;

Time

( Case (

am ; Left (string ; 2 ) ; pm; Left ( string ; 2 ) + 12 ; Left ( string ; 2 )) ;Right ( string ; 2) ; 0 ) ; "" ;

Time (

Case (

aam ; Left (string ; 2 ) +12; ppm; Left ( string ; 2 ) ; Left ( string ; 2 )

);

Middle ( string ; 3 ; 2 ) ;

Right (string ; 2 )

)) ; number))

[egulk2000]

i found the answer!!!

 

its on the forum i got this from originally..

 

http://filemakertoday.com/com/showthread.php?t=16744&page=3

 

and kjoe' s works better than CobaltSky' s because CobaltSky' s calculation doesnt return the 1200am to 12:00 AM but to 12:00 PM, and 12am returns 12:12 PM, which are pretty big bugs, i think...

 

but thanks a-ton, for all your help, i really truly appreciate it!!