search within portal

[fmpTezz]

Hi All,

My dbase has invoice, invoice line item and item tables. (And others...)

I have a portal in 'invoice' which is selectable from a drop down box.

I would like to make this list only contain items that are available -

thru searching with the date the item is due to go out and the date the item is due back, to ensure that only available items appear in the portal list. (these parameters have previously been entered)

I believe this is not possible. Is this true? I can't think of any other way to achieve this. Any suggestions??

Thanks

Terry

[aaa]

Your question is not clear. Post closely about your problem.

[Zadkin]

'I believe this is not possible. Is this true?'

As an indexed value is needed as key to include a record in a portal, it is only possible to work with stored calculations. The two dates are not a problem, but comparing them to 'today' makes the calculation not storable. So it is not possible.

But if you run daily at startup a script that does the calculation, the problem is solvable. (Optimisations on, has the script run today, run only on critical records, are possible) So it is possible

[comment]

Of course it's possible. There is no need to compare the dates to 'today' in the related file. The relationship can make the comparison.

Terry:

Your question indeed lacks some details. Please explain exactly what parameters have been entered where, and what are the conditions to make an item available.

[fmpTezz]

Ok Sorry. I'll try to be clearer. I've posted my database too if you want to look. USERNAME - "Admin" PASSWORD "a"

Background:

The purpose of the database is to manage equipment in a busy university storeroom. (eventually all around our small campus.) There is not enough equpment to go around. So Clash checking is important.

It is not like a video library where you can take anything on the shelf... Staff want to book items for 13 teaching weeks in a row and be sure they will get them - like a video/tv trolley for their class. - and lock this in at the start of the semester.

Information will be entered in the following order:

Customer (Either scanned from a card or selected from a list)

Hire Dates (and Times)

Equipment selection.

Equipment selection happens from a portal - from Line item and Item tables.

When the first box in the portal is clicked, a drop down list appears of all the items. (Currently working OK)

I would like this list to only display items that are available - to prevent double booking. This is my problem.

This will be determined by searching the Line Items Table for items that are signed out during the dates that the user has entered, and removing these items from the pop up menu.

(The other 2 paramaters that I would like to enter here are to also search for times as well as dates for the item, as some items are used multiple times in a day, and to use a field "Is this item Borrowable?" as some items are permanently allocated - but if I get an idea of how to do the dates, I should be able to do this.)

As I suggested, I am open to any idea as to how to do this.

Thank you all for your comments. I'm overwhelmed by the generosity you all have with your time and the help you give struggling FMP designers.

Terry

VCAEquipment7.zip

[comment]

I am attaching a rather primitive demo of the principle.

However, by your description I am afraid you will need a much more complex solution. Resource booking, and especially double-booking prevention is hard to do. Perhaps someone else can offer a more mature template.

LoanItemsVL.fp7.zip

[RalphL]

Look at the Template Lending Library that comes with FMP7. It uses 3 where 4 tables are used by fmpTezz. Assets = Items, History = LineItem. Contacts = Customers. Invoice would replace Contacts in the graph.

Note how the graph is divided into 3 groups (TOG's), one for each set of layouts.

[fmpTezz]

Thanks Guys,

Let me see if i have this more or less straight...

From the example from Comment:

You have cleverly used the inequality of the 2 instances of the 1 table "item".

You have said that "Free Items" are items where the field cDatesR is not equal to the start date or end date.

The field gItemID is populated from a list "UnAssignedItems" which is taken from the "FreeItems" Instance of the table.

You have used a repeating field cDatesR to list all the dates where an item is booked.

Thank you very much for the post. I would never have thought of this. I have learned quite a few new things from your post. I think i could get this to work in my Db.

Can anyone suggest a way that times could be incorparated in this also?

Would it work somehow to convert the date to a number, (with date 1 commencing 1 jan 1904 is the standard i think?)

Convert times to a number, in say 1/2 hr allotments in a day, and put these 2 numbers together and use this new number , the combination of date and times, to populate the list?

EG say 20 mar 2004 is date no 19000. Say i want to borrow an item from 1pm -5pm.

This is 8 half hour blocks. these blocks commence at midnight and so 1-5pm is 26-34

put these together (Perhaps with a decimal point between?)and the field that is CdatesR at the moment would have 8 entries for this 4 hour booking. These would be:

19000.26

19000.27 ....

thru to

19000.34

And use these to validate times in the day?

If a booking went for a month the no of repetitions would be huge (48 x 30 = 1400)WOuld this number of entries in the field bring the database to a grinding halt?

Any ideas for a cleaner solution?

Thanks

Terry

[fmpTezz]

Thanks Guys,

Let me see if i have this more or less straight...

From the example from Comment:

You have cleverly used the inequality of the 2 instances of the 1 table "item".

You have said that "Free Items" are items where the field cDatesR is not equal to the start date or end date.

The field gItemID is populated from a list "UnAssignedItems" which is taken from the "FreeItems" Instance of the table.

You have used a repeating field cDatesR to list all the dates where an item is booked.

Thank you very much for the post. I would never have thought of this. I have learned quite a few new things from your post. I think i could get this to work in my Db.

Can anyone suggest a way that times could be incorparated in this also?

Would it work somehow to convert the date to a number, (with date 1 commencing 1 jan 1904 is the standard i think?)

Convert times to a number, in say 1/2 hr allotments in a day, and put these 2 numbers together and use this new number , the combination of date and times, to populate the list?

EG say 20 mar 2004 is date no 19000. Say i want to borrow an item from 1pm -5pm.

This is 8 half hour blocks. these blocks commence at midnight and so 1-5pm is 26-34

put these together (Perhaps with a decimal point between?)and the field that is CdatesR at the moment would have 8 entries for this 4 hour booking. These would be:

19000.26

19000.27 ....

thru to

19000.34

And use these to validate times in the day?

If a booking went for a month the no of repetitions would be huge (48 x 30 = 1400)WOuld this number of entries in the field bring the database to a grinding halt?

Any ideas for a cleaner solution?

Thanks

Terry

[fmpTezz]

Thanks Guys,

Let me see if i have this more or less straight...

From the example from Comment:

You have cleverly used the inequality of the 2 instances of the 1 table "item".

You have said that "Free Items" are items where the field cDatesR is not equal to the start date or end date.

The field gItemID is populated from a list "UnAssignedItems" which is taken from the "FreeItems" Instance of the table.

You have used a repeating field cDatesR to list all the dates where an item is booked.

Thank you very much for the post. I would never have thought of this. I have learned quite a few new things from your post. I think i could get this to work in my Db.

Can anyone suggest a way that times could be incorparated in this also?

Would it work somehow to convert the date to a number, (with date 1 commencing 1 jan 1904 is the standard i think?)

Convert times to a number, in say 1/2 hr allotments in a day, and put these 2 numbers together and use this new number , the combination of date and times, to populate the list?

EG say 20 mar 2004 is date no 19000. Say i want to borrow an item from 1pm -5pm.

This is 8 half hour blocks. these blocks commence at midnight and so 1-5pm is 26-34

put these together (Perhaps with a decimal point between?)and the field that is CdatesR at the moment would have 8 entries for this 4 hour booking. These would be:

19000.26

19000.27 ....

thru to

19000.34

And use these to validate times in the day?

If a booking went for a month the no of repetitions would be huge (48 x 30 = 1400)WOuld this number of entries in the field bring the database to a grinding halt?

Any ideas for a cleaner solution?

Thanks

Terry

[comment]

I don't mean to discourage you - but you are up against a challenging task. I have tried various approaches to this, and I am still looking for the ultimate solution. See the discussion in this thread.

Having said that, it is not too difficult to build something that works adequately. It's just a matter of compromising on some of the features.

As for your questions:

Filemaker's date already IS a number. It is the number of days elapsed since Jan 1 0001. Same with time (# of seconds since midnight). So, for example:

GetAsNumber( Datefield ) + Round ( Timefield /1800 ; 0 ) /100

will return 731660.26 when Datefield = Mar 20, 2004 and Timefield = 1:00 pm, plus/minus 15 minutes.

You could also utilize Timestamp in a similar fashion - whatever is more convenient.

But in fact, with version 7 you can take advantage of both compound criteria and ranges in relationships, so you can save on the keys generation. So for example, a booking for the entire month of March 2005, between 1:00 and - 5:00 pm, might be entered thus:

StartDate: 3/1/2005

EndDate: 3/31/2005

StartTime: 1:00:00 PM

EndTime: 4:59:59 PM

and the relationship to catch a conflict might be:

StartDate1 <= EndDate2

AND

EndDate1 >= StartDate2

AND

StartTime1 <= EndTime2

AND

EndTime1 >= StartTime2

(I hope I got it right). See the abovementioned thread for a disscussion of pros and cons of this approach.

[comment]

I don't mean to discourage you - but you are up against a challenging task. I have tried various approaches to this, and I am still looking for the ultimate solution. See the discussion in this thread.

Having said that, it is not too difficult to build something that works adequately. It's just a matter of compromising on some of the features.

As for your questions:

Filemaker's date already IS a number. It is the number of days elapsed since Jan 1 0001. Same with time (# of seconds since midnight). So, for example:

GetAsNumber( Datefield ) + Round ( Timefield /1800 ; 0 ) /100

will return 731660.26 when Datefield = Mar 20, 2004 and Timefield = 1:00 pm, plus/minus 15 minutes.

You could also utilize Timestamp in a similar fashion - whatever is more convenient.

But in fact, with version 7 you can take advantage of both compound criteria and ranges in relationships, so you can save on the keys generation. So for example, a booking for the entire month of March 2005, between 1:00 and - 5:00 pm, might be entered thus:

StartDate: 3/1/2005

EndDate: 3/31/2005

StartTime: 1:00:00 PM

EndTime: 4:59:59 PM

and the relationship to catch a conflict might be:

StartDate1 <= EndDate2

AND

EndDate1 >= StartDate2

AND

StartTime1 <= EndTime2

AND

EndTime1 >= StartTime2

(I hope I got it right). See the abovementioned thread for a disscussion of pros and cons of this approach.

[comment]

I don't mean to discourage you - but you are up against a challenging task. I have tried various approaches to this, and I am still looking for the ultimate solution. See the discussion in this thread.

Having said that, it is not too difficult to build something that works adequately. It's just a matter of compromising on some of the features.

As for your questions:

Filemaker's date already IS a number. It is the number of days elapsed since Jan 1 0001. Same with time (# of seconds since midnight). So, for example:

GetAsNumber( Datefield ) + Round ( Timefield /1800 ; 0 ) /100

will return 731660.26 when Datefield = Mar 20, 2004 and Timefield = 1:00 pm, plus/minus 15 minutes.

You could also utilize Timestamp in a similar fashion - whatever is more convenient.

But in fact, with version 7 you can take advantage of both compound criteria and ranges in relationships, so you can save on the keys generation. So for example, a booking for the entire month of March 2005, between 1:00 and - 5:00 pm, might be entered thus:

StartDate: 3/1/2005

EndDate: 3/31/2005

StartTime: 1:00:00 PM

EndTime: 4:59:59 PM

and the relationship to catch a conflict might be:

StartDate1 <= EndDate2

AND

EndDate1 >= StartDate2

AND

StartTime1 <= EndTime2

AND

EndTime1 >= StartTime2

(I hope I got it right). See the abovementioned thread for a disscussion of pros and cons of this approach.