Move to next Field???

[johnG]

I�m new(ish) to Alpha 5 having worked with Dataease for many years and would welcome advice as to whether the following is possible in A5.

Table A holds records representing time which are used to calculate the number of half hours slots an activity will take. ( timeend-timestart) converted to seconds then /(30*60) to produce number of slots.. That�s fine and done.
Timefields (shorttime) represent 08.00, 08:30 to 23.30. via simple list lookups.

Now here�s where I loose the plot�
I have set up a �diary� table with blank records for every day of the year with fields to �hold� a character value which then in turns triggers a colour equation so when looking at a browse view unoccupied fields show green occupied red. Each field represents a �time slot�.
The idea being to be able to see which time slots are free and which occupied.
Lets say the first time period is 8:30 the last 10:00 ie three half hour periods. Is there any way to then post the �slots� so they incrementally modify 3 adjacent fields in the �diary� table in a single record.
A massive nested if() might well work but can it be done using a �while loop� or � count� function within a script..
Simply is there a function that incrementally moves to the next field. ( field1, field 2, field 3) . Is there a �move to next field� function�?
Any advice would be welcome. Hopefully this question is posted in the correct section.

[Keith Hubert]

Re: Move to next Field???

Hi John,

Welcome to Alpha. Which Midlands is that?

Is there any way to then post the �slots� so they incrementally modify 3 adjacent fields in the �diary� table in a single record

The 3 adjacent fields will still remain 3 fields but you can change the way they look conditionally. What do you want to fill the fields with?

[johnG]

Re: Move to next Field???

Keith,
Thanks for the reply.
I mustn't have been clear. in my post.
I want to fill the adjactent fields with the same data. The changing of the appearance is not the problems its can the same date be entered into 3 adjacent fields>

In nomral speak take from an input via table A "xxxx" and then enter it in fields 3, 4 and 5 etc in table B the initial field relying on perhaps an if() function to start the posting/ modifying.

[Keith Hubert]

Re: Move to next Field???

Hi John,

Is table B a child table of table A?

[johnG]

Re: Move to next Field???

Keith , I haven�t set it up that way so far because based on my previous experience with Dataease Relationships which equate to Parent Child Links in Sets can complicate issues. However a Link on the Date fields in each Table would work.

I think you need a recap and more information....
Table A contains A) "Date" Field, B) Client "Id ref." and C) "startime" field d) the calculated number of half hour "slots" field.

Table B has a "Date" field which is prefilled so we have one record in table B for each day of the year.

Table B has other fields equal in number to the number of starting times set in Table A "starttime" via a simple lookup list ( 08.00, 08:30 to say 13:00). So a "starttime" value of 08:00 is represented in Tbl B as Field2, 08:30 as Tbl B Field3, 09:00 as Tbl B Filed4 etc.
When a New record is entered into Table A , on hitting a Save button besides recording the data into Table A , I want, via a script, to modify the corresponding record in Table B ( with the same "Date" value) so that the Client "Id" ref from Table A is entered into Table B by way of a modification/update Which field it goes into is determined by its value ie dependent on the �start time� value from Table A. I can do that via nested if() function.

However and this is where I struggle:
If the value held in �slots� from Table A > 1 then I need to enter the same Client� Id ref� from Table A into the fields adjacent to the field in Table B so that the number of fields in a single record with the �Id Ref� equals the number of �slots�. Fields with data in show up red, empty field s green that�s done no issues.
So is there a function or workaround that simply allows within a 'while' or 'count' based on the value held in Table A �slots� this sequential entry ie enter first value into specified field and then into the next (x) fields to the right in the Table B structure. So far I haven�t identified an appropriate function in Xbasic.

Midlands is West Midlands UK.
Thanks for your interest.

[Mike Wilson]

Re: Move to next Field???

John,
This is certainly doable, but it is not simple. I am at this moment envisioning needing maybe several if staments, probably at least one for...next loop, and the use of the eval() and evaluate_template() functions. A check system will also have to be developed.

If you could generate a mock example of the Table A starting record and what you expect to have the outcome in Table B, giving all the scenerios that you envision, I am sure a user defined function can be achieved. The questions that need to be answered right away in my mind are (please pardon me if you already answered this and I didn't pick it out):

1. Since the Table A record has a client_id field, the "slots" for a Table A record are exclusive for that client...BUT... this is not true for table B. A table B record is actually a compilation of the all the times of all the records for a given from Table A? Correct?

2. Why do you believe that you need an "adjacent field" to hold the value for the client_id in Table B? Why couldn't it be that the field contains the client_id value and be green at the same time?

3. Each slot field in Table B equates to a 1/2 hour period. Is this ALWAYS on the hour and 1/2 hour? How many slot fields are in a Table B record?

4. Are you envisioning populating table B with all the table A records for a given date as a batch function addressing all the Table A records for a given date, or an event to handle only one Table A record at the completion of that record?

5. Are there time slots restricted to being assigned (lunch, weekly meeting time, tea time) other than the slot is already assigned to a client?

Enough for now?

[johnG]

Re: Move to next Field???

Mike thanks for the reply and thanks for your interest.
Answering your questions
1) Correct
2) see xls file
3)Yes see xls file
4)Enter / Modify record in B on each save in A not batched.

I hope tha attached helps.

[Peter.Greulich]

Re: Move to next Field???

John,

Your Excel file is very clear. If the fields are really as few as you suggest, why do you even need Table A? Just enter the data directly into Table B?

Otherwise, I would do it like this:

• Table A would have logical fields for each time slot, corresponding to table B.
• A button on the Table A form would run a script (function, whatever) updating Table B.
• The script examines each logical time field in the Table A record. If true, find the corresponding date record in Table B + find the corresponding time slot (field) in B and change the record to add the Client_Id.
• The script would also clear any values in the Table B record if a time slot is unchecked (that way Table B would be properly updated as needed).

Your model, as presented, assumes no duplicate time slots on any given day. IOWs, you wouldn't have 2 clients with a meeting (or whatever) scheduled at the same time on the same day.

[johnG]

Re: Move to next Field???

Peter thanks for your input.
Table A does contain other fields and will fulfill other functions to the extent that the data in Table A holds required discreet information that is needed .

I left the other fields out of Table A for the purposes of this appeal for help as they are unnecessary.

Apologies if that confused anyone.

[Mike Wilson]

Re: Move to next Field???

John,
Hi. Thank you for the clarification and nice excel file. I proceeded without that and I believe came through with what I interpreted being that which you described. The enclosed example is what I came up with. I did this on the ferry across the Great Lake of Michigan en route to visiting my sister for the Memorial day weekend. A bit hug-over from Karyoke last night I might admit on the way home, but see if this is on track with what you envisioned. The form that opens on database start up (F_B) is the scheduling form based upon table_b. The button on the form runs a User Defined Function (UDF) that courses you through selecting a client, selecting a date of scheduling, and then generates the possible available time slots for the start time and after choosing the start time, generates the available slots for the end time to select from. This is then writen to table_B which the browse is based upon and also tabluated to the Table_A after it writes to table_B. Kind of like what Peter was saying. I see that there are other checks, balances, and catches that are needed, but although certainly unpolished, I think this demonstrates that what you wish to achieve is certainly possible.

Holler back with any questions.

[Mike Wilson]

Re: Move to next Field???

Additionally...
if you double click on the slot assigned in the schedule browse, it will display the owner of that time slot into a timed modeless dialog box.

[Keith Hubert]

Re: Move to next Field???

Mike,

Another of your master pieces, akin to the Airplane booking app.

[johnG]

Re: Move to next Field???

Mike,
Very many thanks for your time and interest. I have downloaded the zip and will be looking at it shortly ( seems I need to cut our small patch of lawn before peace and quiet reigns!).
Its highly encouraging to know that there are people prepared to help 'beginners' in this way.
I'll post again when I've taken in what you have sent! Hope the head is better!
JohnG

[johnG]

Re: Move to next Field???

Mike,
I have now looked at the contents of the zip and what you have produced is pretty much 'smack on the money'.
What its taught me is that I need to think in a different way ie try to ignore everything I learnt about assembling databases in Dataease.
Again many thanks... now back to reading.

[johnG]

Re: Move to next Field???

Mike,
Can I take you up on your offer of a little more help.
I've used 'your solution' and amended the Table_b to include an extra field "Pitch_No"
I'm a member of an amateur sports club and we will use the database to log in bookings hence the 30min time-slots and now pitches
I've repopulated Table _B so that it now holds 3 Records for each date but with a differentiating character ( number) under Pitch_No. We have 3 pitches so
s_date Pitch_No
29/05/2009 1
29/05/2009 2
29/05/2009 3
30/05/2009 1
etc
Can I amend FUNCTION slot_times_assign AS c()
I think Line 72 or 73
from
query.filter="s_date ={"+ dtoc(vdate)+")"
so that it includes an extended filter based on value of Pitch_No and s_date.

I can then either replicate overall "Function Scipt" for each Pitch_No on seperate buttons or use a multifunction button?
So far I keep coming up with problems around syntax I think from mixing field types.
There again I may well be way off beam altogether.
John