Stock control over a network

[John Keogh]

I have an application that works flawlessly for me for my hire service stock control on one workstation.

Presently I hire goods to customers between two dates ie date of collection and date of return

I run a query of all bookings and mark a logical field true for all records that match. Then post the true result to a logical field in my stock table. At the bookings form I have a stock code lookup which has a filter to exclude all the records marked true so what we see is available stock for the given dates.

My problem is when I introduce another workstation. It also sets the same logical field to true so records are being filtered out that may be available.

Any thoughts on how to overcome this will be appreciated

Regards

John

[Peter.Greulich]

Re: Stock control over a network

I don't think you need the logical field at all. Do the same thing you are doing but apply variables to filter out the records in the lookup (instead of the logical field). Then your other workstation(s) should remain unaffected.

[John Keogh]

Re: Stock control over a network

How will the filter know which records to exclude. Booked stock has no dates set against it

John

[Peter.Greulich]

Re: Stock control over a network

How will the filter know which records to exclude. Booked stock has no dates set against it

Then, how do you run a query to mark the records?

[John Keogh]

Re: Stock control over a network

The query is run against all bookings a logical field is set to true and posted to the stock table. How can I achieve this with out the logical field. If I post the dates to date fields any other workstation will overwrite the dates. Unless I am missing something here I cant see how this will work without my logical field

John

[Peter.Greulich]

Re: Stock control over a network

Sorry John. Something is not getting through. I thought you had two date fields: date of collection and date of return? Maybe you need to explain your table structure in a little more detail. Are we talking about one table or two?

[John Keogh]

Re: Stock control over a network

We are talking 2 tables.

A booked table and a stock table.

Booked has date of collection, date of return and a logical called "booked" which is set to true.

Stock has only the logical.

The posting is of the logical from the booked table to the stock table.

Then I filter out the "booked" in the stock table when the lookup is performed.

Works great with one workstation serious problems with more than one

John

[Peter.Greulich]

Re: Stock control over a network

I am sure there is a better way to do this, but it eludes me. I'd have to think really hard and that would hurt my head. So how about an alternative approach? Forget about the lookup. As I understand it, the goal is to see which stock items are available on a certain date. How about creating a 3rd table and writing to that? The table might be called Stock_Dates. Whenever you book a stock, using xbasic write all the individual dates for that period to a date field along w. the stock_id in the Stock_Dates table. Query the date or date range and that will give you items booked for those dates; send that to an array. Compare the array to the stock table and filter out the excluded records. Using xbasic, you can make this be seamless and generate an embedded browse view of available stock. Then pick your item ans write it to the bookings table for yor desired date. It is convoluted, but you can make it appear to be seamless...

(But, I'm sure there is a better way?)

[John Keogh]

Re: Stock control over a network

I'm not sure I have the ability for this but I'll give it a go

John

[Peter.Greulich]

Re: Stock control over a network

Forget about the array (I'm thinking fast here which is how I make most of my mistakes). Write the values to a variable, then use the $ operator for your final query.

So assume you have a variable called vBooked. Assign the booked stock ids to that var with a space in between, so the contents of the var look like this:

vBooked = "1001 1003 1037"
where the nos represent stock_ids

Then your final filter might look like something like this:

query.filter = "(Stock_ID $ vBooked) = .F."
assuming stock_id is the name of the field in your stock table

So all the values that are not in vBooked will filter out, displaying available items for your dates of interest.

However, if you have thousands of booked stock items, you might have to do something a little different (due to var size limitations).

[John Keogh]

Re: Stock control over a network

Thanks for the help Peter

Unfortunately your solution is beyond my abilities.

How would I go about starting this

I am not asking you to do it for me just point me in the right direction. I enjoy the challenge of Alpha.

I had been thinking along the lines of appending stock code and the two date fields to a third table and building a filter on the dates similar to the one I use at present for my initial query.

Any thoughts on this approach

John

[DaveM]

Re: Stock control over a network

John,

Suggestion? Post your app here with some sample data or send it to one of the members who has time to look at it and I bet a wonderful solution will be in the works. Probably more solutions than you expect.

[John Keogh]

Re: Stock control over a network

Hi Dave

Would it be the whole application or just the tables in question that I would post?

John

[DaveM]

Re: Stock control over a network

If you can seperate out the tables and any and all scripts forms, etc that could have ANY help or hurt to the needed operation, then fine. It is usually better to send the whole thing.

Describe in full detail the operation you need to accomplish and anything already in place that may need to be changed. Details are very important.

If you post it here, you will probably have several different solutions by The end of the day and they may vary a bunch.

Peter is one of the best on here with the type of thing you are trying to do, but others are great too.

[Peter.Greulich]

Re: Stock control over a network

Actually, John. I seem to remember that someone had a near identical problem recently. I never really read the thread but there were one, or possibly more, solutions posted. All of which were no doubt better than my convoluted approach. I think the thread ran 3-6 weeks ago, but my memory is somewhat unreliable with respect to time frames and such. I think it was in the v8 or v7 forum. If someone knows which thread it is, they might post a link here. I'd like to read it.

There are other ways to approach this too. I think Ira Perlow has a method where he copies or reconstructs tables locally on the fly, thus giving the user exclusive access to situations such as yours. But then again, Ira being Ira, his code is super-optimized for near instantaneous results. Thus, his solution, from a coding point of view is much more challenging than my dumb, brute-force approach.