Doing a database for a fishing tourney. Busting my head. Need some serious help with this script.

Boats are chosen with a picker from the Tournaments table as participants. Each Boat has a number of Anglers. Boats and Anglers are related by iD. I created value list of both portals. The Boat list is a list of Boats chosen, and the angler list is a list of the Anglers on those boats. From either or both these lists I am trying to create records in a seperate Table called "payments" related by tourney id.

2 value lists Boat Id's & Angler Id's The Id fields are correct but I need help with looping script.

ie:

boats Anglers

cornelia Marie Al-Captain, Jack

honey hole Sam-Captain, Jim

hoof arted Charlie-Captain, Tom, Dan

Trying to create payment invoices for each angler . The Captain gets 3 invoices, and everyone one else gets 2. 1 Captain per boat, varying amounts of anglers.

-an invoice for "entry fee" to each boat "Captain" The value "captain" is assigned to 1 angler per boat.....

-2 invoices per angler ; side bet 1 & side bet 2

record creation would look like this...

Boat Angler

Cornelia Marie Al Captain Entry Fee

Cornelia Marie Al Captain side 1

Cornelia Marie Al Captain side 2

cornelia Marie Jack side 1

cornelia Marie Jack side 2

Honey Hole Sam Captain Entry Fee

Honey Hole Sam Captain side 1

Honey Hole Sam Captain side 2

Honey Hole Jim side 1

Honey Hole Jim side 2

Hoof Arted Charlie Captain Entry Fee

Hoof Arted Charlie Captain side 1

Hoof Arted Charlie Captain side 2

Hoof Arted Tom side 1

Hoof Arted Tom side 2

Hoof Arted Dan side 1

Hoof Arted Dan side 2

One method would be:

// from Anglers

Loop

  Set Variable[ $id ; ID ]

  Go to layout[ Invoice ]

  New Record

  Set Field[ AnglerID ; $id ]

  Set Field[ description ; "Side 1" ]

  Duplicate Record

  Set Field[ description ; "Side 2" ]

  If[ angler::role = "Captain"

    Duplicate Record

    Set Field[ description ; "Entry Fee" 

  End If

  Go to layout[ original layout ]  // go back to anglers

  Go to Record[ Next; exit after last ]

End Loop

Thank you so much...

How can I store all the id's in Variable. It seems your script doesn't deal with list(anglers) only 1 id. I have a value list of Angler ID's:

say:

111

112

113

114

How can I write the script so it deals with all the values.

My script assumed that you start from a layout based on the Anglers table, with a found set of all records. I thought that would make for a simpler example.

Using a value list, you can use ValueListItems() so your script would look like this:

Set Variable[ $IDs ; ValueListItems(Get(FileName);"AnglerIDValueList") ]

Set Variable[ $count ; ValueCount( $IDs ) ]

Go to layout[ Invoice ]

Loop

  Set Variable[ $i ; $i + 1 ]

  Exit Loop If[ $i > $count ]

  New Record

  Set Field[ AnglerID ; GetValue( $IDs ; $i ) ]

  Set Field[ description ; "Side 1" ]

  Duplicate Record

  Set Field[ description ; "Side 2" ]

  If[ angler::role = "Captain"

    Duplicate Record

    Set Field[ description ; "Entry Fee" 

  End If

End Loop

Thanks, I'm sure that will do the trick...

maybe I'm going about this the wrong way. I have a Tournament Table, I pick Boats from a picker, and I have the angler table related both to tourney and boats picked. The list is a calculation derived from there. Can you use a field calc List(anglers) as you would a avlue list? Would this script fail in a network environment. Should I do it differently?

Edited April 16, 200817 yr by Guest

If you make a table occurrence of Anglers and link it to another table using the cartesian ("X") join, then you can use the List function to show all anglers from the related table. Since I don't know what relation your value list uses, I can't say for sure whether ValueListItems will give you the same result as List. One difference between the two is that List will show duplicates, but if there's only one record per angler then that's moot. There's no reason for it to fail over a network (why would you think that?) but be aware that List requires version 8.5 or higher.