Show 'status' problem

[AliB]

Hopefully some more experienced users can show me the way here...

Basically, my file has 3 tables: events, stock and hires. The 'hires' table contains records which link stock items to events, i.e. each record has a stockID and an eventID field. Relating eventID between the 'events' and 'hires' file allows me quite happily to show all the items from stock associated with the event in a portal. Fine.

However, I want to have a seperate portal on the same layout which shows ALL items from stock, but with a marker in each row indicating the status of each item, relative to the event record currently being browsed - in other words, indicate which stock items are available (or reserved) for the events date range - but I can't get this to work properly.

At the moment, I can see all stock items (filtered with a global 'class' field) but the coloured statusFlag - picked up from the 'stock' table - is always the same.

I figure that I need a calculation that 'knows' what event I'm browsing, and evaluates the status of each stock item depending on whether or not a record exists in the hires table (and then inserts a calculated result - red, amber or green from a global container). What am I missing? Do I need a separate table to handle the flags?

Any advice or help will be appreciated...

[BFrost]

I'll take a shot at it.

Events

In the current file, Events, have a script which sets two global text fields in the Hires file to the Event record's date range:

Set Field Hires::Global Date From to the values in the Event Record to: DateToText(Event From Date.

Set Field Hires::Global Date To to the values in the Event Record to: DateToText(Event To Date.

Hires

In the Hires file, create an unstored calculate when needed boolean number field to see if either a from date or a to date in the Hire file falls within the range defined by the two global date fields set from the Event File. A non zero result indicate an overlap between the two sets of dates:

Date Test =

If(

(Hire From Date is greater than or equal to Global Date From AND Hire From Date is less than or equal to Global Date To)

OR

(Hire To Date is less than or equal to Global Date To AND Hire To Date is greater than or equal to Global Date From), 1, 0

)

Stock ID

In the Stock ID file create a Status relationship to the Hires file based on Stock ID and Sort the relationship in descending order of the boolean result from the Date Test field above so overlapping date range records for that will be listed first.

Create a Container Case calculation field where:

1. a "Reserved" value in the Status field in the Hires file = a system Global amber graphic from a system global container field.

2. a "Hired" value in the Status field in the Hires file = a system Global Red graphic from a system global container field.

3. no match value in the Status field in the Hires file = a system Global green graphic from a system global container field.

Got a headache yet?

Now all you have to do in the Event file is cue the script which sets the global date.

1. Custom scripted Go To Record Buttons (First, Previous, next, Last) and a custom scripted GoTo Layout button used to set the dates for the current record when the layout or record is selected.

2. Maybe change the Global From and Global To look up or calculate based on a relationship in the Hires file that has a field set to equal a calculate when needed "RecordID" in the Events file (distinct from the indexed "RecordID" in the events file. The Hires globals will point to the matching indexed "RecordID" in the Events file and steal the dates from it.

There, I think that will do it. I think I got right.

Edited March 31, 2006 by Guest
Reason for edit: Convert from FP7(8) to FP5, Other compulsive changes.

[AliB]

Thank you!!

I have now got status flags in my stock list portal which update depending on the date range of the current event. Once the dateTest field was added to 'hires' table, I was able to add a flag in the 'stock' table with a case calculation (i.e. if dateTest = 1, use the status from the matching hires record, otherwise set it to globalGreen).

The portal in the 'events' layout doesn't always update, and I've had to script the Go To Record buttons, so whilst it's not as 'live' as I might have hoped, it does work perfectly.

Perhaps it will break with further testing, but I am going home a happy man tonight.

[BFrost]

Thank you!!...The portal in the 'events' layout doesn't always update, and I've had to script the Go To Record buttons, so whilst it's not as 'live' as I might have hoped, it does work perfectly.

Perhaps it will break with further testing, but I am going home a happy man tonight.

You're welcome. I'm glad it's working.

How are you setting the globals? If by script, don't forget to have an unspecific "Go To Field()" as the last step. This will force the current record's values to update.

If it's still not updating right away, post the behavior and conditions leading up to it.

[AliB]

It's like this...

The events layout has a portal showing all stock items, filtered with a drop down menu. Thanks to your input, these items now correctly show whether they are available (green), provisionally booked for another event with conflicting dates (amber), or already booked out (red). I have also amended the 'add stock item to event' script so that it now won't let you add an item to the current event if it is booked elsewhere.

The 'live update' problem only affects this stock portal. My prev and next record buttons use setField to send the eventID to the global (with additional fields that calculats the from and to dates). The stock portal correctly updates when you move from event record to record.

However, if you stay in the same record, and update it's status from provisional to confirmed, the portal won't update until you change the stock category filter (from the drop down menu - forcing a refresh of the portal contents). I have a 'confirm changes' button on the layout which commits records/requests and refreshes the window. Clicking this button will also refresh the portal, but it's a step I thought wouldn't be necessary, hence the feeling that the solution is not as 'live' as I would have liked.

Is it possible to make a selection in either the stock filter or the status drop-down menus and force a refresh without going to the next field??

My next problem is to alert the user when they subequently confirm a booking that affects multiple hires of the same stock item....that might give me a headache!!

[BFrost]

I'm having a little trouble visualizing the problem but it sounds like the old 'go to field()' script step could be put to use somewhere. In fact it could be the only script step associated with an 'update' button. With no field destination specified, 'go to field()' forces an update in a manner that tells FMP your done with the record but you don't want to go anywhere else, next field or a different record.

I sometimes put a clear button over a field (not in the Tab order) where the only way the field can be entered is by clicking the botton activating a script that enters the field.

That same script could require a pause with allow user abort set to 'Off' so the only way to continue would be to click 'Continue' in the status area (if it's visible), or to press the 'Enter' or 'Return' key (if the instruction has been made clear), or clicking an 'OK' button that has a 'go to field()' step in it with the button set to 'Resume' script.

HTH

Are you a fiddler?

[AliB]

Thank you for your continued advice and patience!!

I'm not getting this at all. When I select an event record (prev/next/find), the script sets the eventID in stock and hires. The hires table has hireStart and hireEnd fields which calculate these dates via a relationship to events (eventID::eventID). This works fine - I can see that the eventID and dates change in my hires table depending on which event record I'm browsing. I can also confirm that the dateCheck calculation you suggested works i.e. =1 if there is a date conflict and =0 if there is no conflict.

However (head scratching starts), despite having a relationship in my stock file between stock and hires [stockID::stockID, sorted by dateCheck (descending) as your previous post], the stockStatus container (within the stock table) doesn't update when you move to another event record.

When I open the define database dialogue and check my calculation for this stockStatus field, click OK and return to browsing stock records, it has mysteriously updated.

What am I missing??

Oh, and no - I used to play a little fiddle, but am a Jazz Pianist, Keyboard & Music/Mac Tech guy. And I know increasingly little about FMPro it seems...

[AliB]

I think it's fixed....

The relationship between hires and stock was sorted on the LHS by dateCheck but adding a sort on the RHS by eventID seems to have done the trick!

I only wish I understood WHY - perhaps your superior knowledge could elaborate....

[BFrost]

I think it's fixed....

The relationship between hires and stock was sorted on the LHS by dateCheck but adding a sort on the RHS by eventID seems to have done the trick!

I only wish I understood WHY - perhaps your superior knowledge could elaborate....

I don't recognize what you mean by LHS or RHS.

Is your sort defined by the relationship or by the portal (oops, your using fp5, right).

[Lee Smith]

I think it stands for

LHS = Left Hand Side

RHS - Right Hand Side

;)

[BFrost]

I think it stands for

LHS = Left Hand Side

RHS - Right Hand Side

OH! That LHS and RHS! LOL. Thanks, Lee.

[AliB]

Like he said - LHS = left hand side, RHS = right hand side - sorry. When defining the relationship between hires (L) and stock ® there are options to 'sort' at the bottom of the window (hence left side and right side). Again, I'm not understanding why adding the sort on the right (under stock) made the difference...?

Just to clarify, I'm using FMPro v8 (and Mac OS X Tiger) - the forum list doesn't seem to always show this correctly. No doubt my fault being new to this forum thingy.

Can you tell me if this solution [setting a global eventID & global from/to dates as previously discussed] is going to work in a multi-user environment?? Will I not need to set different eventID globals per user? My train of thought is that when one user selects an event to view, and the script sends the eventID (and by calculation the from/to dates) to the stock & hires tables, this will affect the status marker in the stock list portal? I haven't checked this out yet, but I'm seeing a pitfall.....

Thanks for all your help and advice so far!

[BFrost]

...I'm not understanding why adding the sort on the right (under stock) made the difference...?

...Can you tell me if this solution [setting a global eventID & global from/to dates as previously discussed] is going to work in a multi-user environment?

I don't understand why it would make a difference, either. It might help me to have the database to look at instead of trying to imagine it, though.

I don't see why this wouldn't work in a multi user environment. Do you have a means of testing it now in that regard?