I have a field named task category.

I want it to store the category name with the count of how many of the same type per customer.

For example I have a selection menu pop up (Thanks Bobscott) that has the type selection display Floral, Art, or Misc.

So when I create a child record In my subform (thanks again Bobscott) I choose from the list and  I can see the category each child record belongs to.

So my subform displays a spreadsheet like this customer number, date, task.

Brown8675  Date Floral
Brown8675  Date Floral                              
Brown8675  Date Art
Brown8675  Date Misc
Brown8675  Date Floral                              
                           
What I would really like is the category to be one of the choices plus the number of that type for that customer I am up to. For example the first floral would be floral1 the second floral2 the third would be floral3 etc. The problem is there is no specific order of sales. It could be a floral purchase then 2 art then another floral etc. What I would like is this

Brown8675  Date Floral1
Brown8675  Date Floral2                              
Brown8675  Date Art1
Brown8675  Date Misc1
Brown8675  Date Floral3

So what I tried to do is once the category is selected I tried placing it in a variable.
Then I tried using the basic language to count the previous amounts and then append it to the variable and then send that number to the category field. I tried using

vmyslstotal = 0
vslsvcnt = @FormResultSetTotal("sales")
vslsloop = 1
while vslsloop <= vslscnt
{
  vslstotal = vslstotal + @FormFieldValue("sales", "category type", vslsloop)
  vslsloop = vslsloop + 1

categorytype = vcategorytype + vslsloop

}

Obviously this is not correct. Ideas please? 

Actually I am surprised you think this is unique.

We have arts and craft products that are educational for the mentally disabled. Each step builds upon the previous. Sometimes a customer will work on the project many years. Often physicians will measure the improvement or lack of improvement by the customers progress. So when they start a floral project we will send step1 as they progress over time we send the next step. So floral1 represents step1 of the floral project. Often there are times when they will temporarily switch to a new project called Art. Hence Art1 is step 1 of a project called art. Besides the normal problems when an employee sends the wrong project number it is much worse to the customer who may not have the ability to quickly comprehend that it is incorrect and might spend many weeks being frustrated or giving up for the wrong reason (the reason being our mistake). So when Bobscott helped me setup a spreadsheet type subform we saw a method to give my employees a quick snapshot of activity when a customer calls. I originally set up Floral 1 to floral 86 Art 1 to Art 22 but I was afraid that people will choose the wrong one.

Of course when we thought more about it we realized it would be best if the computer handled incrementing not one of us.  I would have thought many people use things like this for organizational purposes.

Quote:


I would love to see how you would do this also.

before you posted your reply I was sending this to you.

Erika,

The application that I recently sent has a database called Events it is very similar to what sunnyjenna is explaining. From my basic subform on the events tab I list all activity for each specific patient. If you look at that you will see a category element, is there a way to use   @FormFieldValue() and specify a specific match to count in the specified element?

Basically set up 2 elements 1 category element that is set invisible and 1 catagoryshow element that is visible. Upon selecting the basic category, in sunnyjenna case Art or Floral and return that value to the invisible element. Then each time an activity is created it would slug floral or art in the invisible element and then count the matches of floral or art in the invisible element and then combine the value in the invisible element with the count and put the result in the visible element.

I know I oversimplified it but I am just thinking out loud. Am I making any sense?
 
Was I even close to a correct method?

Thanks for the snippet. There is some great stuff in there. I especially like
The method you chose to identify the element content.

If @Left(vVal, 6) = "Floral"

Else If @Left(vVal, 3) = "Art"

Else If @Left(vVal, 4) = "Misc"

So simple, I was so busy searching for something cosmic in Sbasic, I overlooked the old tried and true techniques.

One of my weakness with Sbasic is knowing were and when to use the curly brackets and the other symbols, the structure you use looks similar to many other languages. Can you recommend a good book on the use of programming structure that will be similar to what I need to know for Sbasic?

Thank you for your help.

Erika,

Just for grins I decided to try and implement your snippet into one of my applications. It works absolutely perfectly. (Not that I doubted it for a moment). For the most part I understand exactly what is happening. My one dilemma is I See the numbers being appended to the eventtype element when I run the application. What I do not understand is exactly what part is handling the return of the updated data to the element.  I see for example vVal = "Sleep" + @Str(vSleepCnt) but I was expecting to need to add something like Eventid = vVal. I figure this FormFieldValue("events", "eventtype", vLoop, vVal) vLoop = vLoop + 1 is the line doing the updating but am not 100 percent sure.


This is the code I am using that works well. I tried documenting what is happening as it runs. The temporary writeln() help to see whats happening.

Could you please explain how the eventtype value is being updated.

Thanks. (I bet when you decided to create software you never realized you were going to have to be a teacher of programming at the same time )


// Here I am declaring my variables
var vsleepCnt as Int
var vDmeCnt as Int
var vMiscCnt as Int
var vTotal as Int
var vLoop as Int
var vVal as String

// This is triggering my event  (when all subform event timming is corrected this is
// probably not needed

If  EventType = "" and  neweventtrigger = "S"

then

{
//  stoping event from running again undesired.
neweventtrigger = "c"

// capturing eventtype choice
eventtype = @PopupMenu("Sleep;DME;Misc","Please Select Event Type")


writeln(" capturing eventtype choice = " + eventtype)

//resetting counters

vSleepCnt = 0
vDmeCnt = 0
vMiscCnt = 0
vLoop = 1
vTotal = @FormResultSetTotal("events") // counting amount of lines

writeln("vtotal counting amount of lines = "+ vtotal)

// telling it to run until all lines are counted
While(vLoop <= vTotal) // telling it to run until all lines are counted
{
vVal = @FormFieldValue("events", "eventtype", vLoop)
If @Left(vVal, 5) = "Sleep"
{
  vSleepCnt = vSleepCnt + 1   
  vVal = "Sleep" + @Str(vSleepCnt)
  writeln("vval of sleep = "+ vval)
}
Else If @Left(vVal, 3) = "Dme"
{
  vDmeCnt = vDmeCnt + 1
  vVal = "Dme" + @Str(vDmeCnt)
  writeln("vval of dme = "+ vval) 
}
Else If @Left(vVal, 4) = "Misc"
{
  vMiscCnt = vMiscCnt + 1   
  vVal = "Misc" + @Str(vMiscCnt)
  writeln("vval of misc = "+ vval)
}

// is this returning the value to each element?
FormFieldValue("events", "eventtype", vLoop, vVal) vLoop = vLoop + 1
writeln("vloop of last if = "+ vloop)

}
}

Now that I have this really nice table subform that lists all the activity with an event type and number pertaining to a specific patient it would be nice to be able to select any event on the subform and be transported to a form or subform that has more in-depth detail on that specific event.

The subform basically looks like this this:

Action      Started Date      Type     PT ID#     Status   event ID#
1 C               01\09\2004       ECP1       976            80          2876
2 C               01\17\2004       Sleep1     976            77          3115
3 C               02\11\2004       ECP2       976            86          3196
4 C               02\16\2004       Sleep2     976            90          3454
5 C               03\09\2004       Sleep3     976            80          3877
6 C               03\19\2004       Dme1      976            77          3901
7 C               04\05\2004       ECP3      976            80          3983

I was thinking that since the event id is entered when the event is created without going into the event id element I would put on element entry logic into the event id element that when clicked into would capture the unique eventid and use Sbasic or a macro to take me to the form or subform that has the in-depth detail then use that eventid to search and bring up the data in detail.

Any thoughts on what method Sbasic or macro or ? I should play with to achieve my goal.

Do you see any pitfalls I am blissfully walking into?

Thanks