Calcs to block dates

[crazybake]

Not sure the best way to handle this relationship or repeating field....

A have an assets table, a transactions table and a lineitems table....

An individual can checkout an item or reserve an item. The way I did this is to set a value to lineitems::TransactionStatus to "1" for checkout and "2" for reserve (and "0" for in stock)

When an individual attempts to checkout or reserve on a specific date, I use a find request and set the value to:

-For checkout: Get(current date) and "1" which will see if it checked out or in stock or

-For reserve: ReserveDate and "2" to see if it is reserved for that date - (and they are not allowed to reserve on the current day)

I have reading about setting variables. Should I have done it this way - or a different way altogether? This may help me in determining the calculation method that I cannot figure out.

Each asset::CheckoutLength will be : 1 day, 2 days or one week from the transactions::transactionDate.

So lIneItems::TransactionDueDate I have set as an auto calc.

If an asset is reserved for 1 week, I tried to use a repeating field to "fill in" days in between (say I reserve ItemA on the 1st, it will be blocked from any reservation until the 8th - so the repeating field has entries from the 1st through the 8th.) Is this the right way? It seems to find correctly if I try to reserve on any of those dates. However, I want to be able to have a script get that repetitions value - whichever it is (say if try again to reserve on the 4th- I want it to find the repetition that has the date of the 4th.) This will also be the issue if they return the item early, I will need to remove the reserve dates... I think..

stuck.

[comment]

A have an assets table, a transactions table and a lineitems table....

The role of these tables is not quite clear. It seems you should have a table of Assets and a table of Transactions - where a transaction can be either a reservation or a checkout, with a start date and an end date.

An asset being in stock or not is an attribute of an asset, not of a transaction involving that asset. To find out if an asset is available in a given date range, you can perform a find for any transactions that overlap the range (or use a range-to-range relationship to do the same thing).

See also:

http://fmforums.com/forum/topic/70312-records-between-2-dates/page__p__333136#entry333136

http://fmforums.com/forum/topic/61691-please-help-very-simple-calendarappt-manager/page__view__findpost__p__291727

[crazybake]

I do have more tables and relationships...

The main tables are:

Customers, Assets, LineItems and Transactions.

The main reason for the lineItems was so that one transaction could have multiple assets checked out. Sometimes there may 4-10 items at a time and each of these items may have different due dates or reservation dates. There also might be items they have reserved and also that they want to checkout addditionally at the same time.

I have a portal for lineitems on the transactions layout to accomplish this.

The relationships are:

Customer:CustomerID::Transactions:CustomerID

Assets:ItemID::LineItems:ItemID

Transactions:TransID::LineItems:TransID

I thought that the lineItem needed to have the "reserved", "CheckedOut" or nothing tag so that one asset could have multiple reservation dates ("2") and at the same time be checked out ("1") - I guess I didn't think it through clearly.

[comment]

OK, then what I said about Transactions applies to LineItems. In any case, the availability of an asset is a result of ALL its line items, so it needs to be calculated.

[crazybake]

Ok, I have all of my find creteria except the main problem I am having.

If an individual has a reserve transaction and that asset has a checkout or reserve length of 1 week, what is the best way to " block the week."

right now I have the reserve date as a repeating field so it will show up in each day as blocked. If they reserve on the 1st, it will add date entries into Each repetition until it reaches 7 days. It also shows up in my calendar view this way (a bunch of potals with date filters.)

What is a better approach?

[comment]

I am not sure what you mean by " block the week."

[crazybake]

Sorry..

Assets have different reserve or checkout lengths.

If I set AssetA to have a length of 7 days. A user then reserves that Asset on 8/20/11. That means that nobody else should be able to reserve or checkout that item between 8/20/11and 8/26/11.

Right now (without using a repeating field), I am able to perform a find that will find all reservations based on a single reserve date (8/20/11.) however, I can't figure out the syntax that will not allow reservations BETWEEN 8/20/11and 8/26/11

[comment]

Instead of the repeating field, calculate the end date of the reservation. Then you can perform the find as explained in the first link in my first post.