how to use an array

[John Keogh]

Re: how to use an array

I'll have a go at this.

Previously I was thinking of having a second table called stock_date each record would have stock_id, date_collect and date_return fields.

If I used a lookup filter similar to what I using presently to get my logical field booked set to true would this do away with the need for an entry for each day.?
This is assuming that I can get the lookup to filter on values from an unrelated table!

[MoGrace]

Re: how to use an array

I'll have a go at this.

Previously I was thinking of having a second table called stock_date each record would have stock_id, date_collect and date_return fields.

If I used a lookup filter similar to what I using presently to get my logical field booked set to true would this do away with the need for an entry for each day.?
This is assuming that I can get the lookup to filter on values from an unrelated table!

I have to admit I am thinking something akin to tuxedo rentals here. Showing a particular item as booked would prevent you from booking it again for a future time when its current status is still booked. That is why this needs to be tested elsewhere so that your items table is merely the lookup to provide the detail for the item. Can you zip up an example for us to play with?

[John Keogh]

Re: how to use an array

Tuxedo rentals is exactly what I do but in Scotland its kilts, highland jackets and all the accessories that go along with it.

Clothing, tools, cars or hotel rooms they all have a start date and an end date in between these two dates they are unavailable to anyone else, only the booked user can have them allocated to them.

John

[John Keogh]

Re: how to use an array

Away from work now will see if I have a backup at home and zip it other wise it will be tomorrow.

Thanks to all for all the input. I feel I am getting close to a solution now.

John

[John Keogh]

Re: how to use an array

Here is a zip of my app. Loads of live data on it.

Its a few months old now things have moved on a bit but the principal parts are still the same.

John

[John Keogh]

Re: how to use an array

Here is a zip of my app. Loads of live data on it.

Its a few months old now things have moved on a bit but the principal parts are still the same.

John

[NoeticCC]

Re: how to use an array

Hi,

May I ask, how many tables has this database got? Because I am just failing to see why you need to use this logical field at all - wouldn't it make more sense to have a table for bookings that contains the item code/id, who booked it, a start & end date and whatever else you need to log.*

Then when you query whether item X is free on date Y, you search for entries in the bookings table that match the item ID and where start_date >= querydate .and. end_date <= querydate... If a matching record is found, the item can't be booked for the selected date, if no matches are found, and the item is in stock (see below - being due back isn't the same as BEING back, so you may want to display a warning to say the item is currently overdue for return and may not be available), then it can be booked for the date(s) you selected.

* It would make sense to set a field to true or false to denote current stock status, this is updated when an item is actually handed out or received back - this also allows you to compare actual stock vs. what is due in, e.g. it lets you determine overdue items etc.

[John Keogh]

Re: how to use an array

Hello

There are several tables being used but the main part uses personal_details and two stock tables kilt_stock and jacket_stock.

Some of the tables in the zip are redundant now. To decrease query time I have split some of the accessory stock tables into several small tables. These tables have no date function part they are simple postings to reduce stock levels by one for every true record between date_of_collection and date_of_return.

The personal_details table has both date fields, stock code fields and all the other info that I need to record.

I think I understand the reason for this unique field that would be indexed but I need in my lookup to exclude all booked stock so I can see at a glance what is available rather than going through stock codes still I find one that is available.

This assumes I understand what is being suggested

Thanks for the input

John

[Keith Hubert]

Re: how to use an array

Hi John,

I can see why you were asking about an array.

What I think you need, as Tom, Robin and Andrea have suggested, is a single table for the lookup ( it would have other benefits for reporting) that would be as if it were a set with the Item and booked dates as the child linked 1:1

So when you do your lookup it would be for Item_Id .AND. Start date of required hire that is .NOT. between Hire date and return date. This would take into account future bookings. As you cant do a look up to a Set it seems to me you will need two look ups one to see if the item is available and the second to book it for hire. When you do the hire a small routine will be needed to append the new data to the booked items lookup table.

Past hiring could be appended to a history table and then deleted to keep the lookup table small and current.

I hope my two pence worth gives an extra thought.

[John Keogh]

Re: how to use an array

Thanks for your thoughts Keith

I had not thought on two lookups.

I'll give it a go.

Right now I'm trying to use exist(), keyexist() and keyunique() to solve my problem.

Do you know if a filtered lookup can be achieved using a filter on an unrelated table i.e.

If my filter was to exclude record that are true in a booked_stock table while looking up my stock table

Regards

John

[John Keogh]

Re: how to use an array

Hello all

Just to update you all on my progress

I almost have it cracked. I can now do my lookups on two machines.

I duplicated my stock table called stock_two and set my lookup to conditional on the name of the pc

Therefore pc1 looksup stock_one pc2 looksup stock_two.

I now have a conditional post again on the pc name which posts to the appropriate stock table setting my logical field to true

Thus my filter to show only .f. records means I see only stock available.

I am refining it so as if the two machines dates overlap they will then post to the others stock table as well.

Its maybe a long winded method but it appears to work.

John

[MoGrace]

Re: how to use an array

Hi John,
You have an interesting project for sure. I have been looking at your tables this past week and seeing if I could normalize them. And now I have a question about the stock qty field for your stock items. I can see where socks and such would be just one item with a quantity on hand field (I assume these are not returned but sold instead?). But each jacket, kilt and other accessories for instance, would need to be a separate item would it not - since these are returned? So some items would only be separate items for size and/ or color with a QOH field (like scrunchies).

So for socks, you would need one item per color and size; and if these are sold then the type of inventory they are needs to be different since these items must be replenished thru new purchases. If this is not true for socks, is it true for any of the other items you carry? It seems obvious that if some item is not returned that was supposed to, then it would get "sold" too and must be removed from inventory or repurchased.

You mentioned Quickbooks in another post. Have you looked at how they handle inventory and non-inventory items?

It would no doubt help if I knew what sporrans, skeans, flashes and scrunchies actually were! ;) I did look online but most times it was assumed the person looking to hire such things knew what they were already.