Creating a Value of Current PK's

[Eden Morris]

At the moment in order to make a multi key of the PK's in a current found set I make a script that loops through the records and add's their PK to a vartiable. Is there a more efficient way of doing this? i just like avoiding using a script if I dont have to.

[comment]

to make a multi key of the PK's in a current found set

For what purpose?

[bcooney]

At the moment in order to make a multi key of the PK's in a current found set I make a script that loops through the records and add's their PK to a vartiable. Is there a more efficient way of doing this? i just like avoiding using a script if I dont have to.

You'll need a script. I believe the fastest way* is to create a layout with just one field (the primary key field). Then go to that layout and copy all records (it'll copy all the keys). Then paste into a global field. However, as comment asked, why? This is obviously a part of a technique, so what's the goal?

*It depends on the size of the found set.

Looping is nice in that you don't need an extra layout. Still, optimize the loop by making sure that you're in a form view, and freeze window.

[Eden Morris]

I have invoice lines and each invoice line can be assigned to an employee for commission. I run a report of all the invoice lines that are attached to invoices that have no balance ( the customer has paid therefore I can pay commission ). I then make a record in my Paid commission table that has a foreign multi-key of the invoice line PK's I give the paid commission record a total amount paid in commission and now those lines show being paid.

The reason I assign commission per invoice line is so that one employee can get paid for up-selling and another can get paid for doing an onsite related to the same invoice.

Is there a better way of doing what I'm doing? :)

[comment]

Is there a better way of doing what I'm doing?

There are faster ways, none of them necessarily better. I would stay with the loop (subject to what Barbara said about optimizing), unless you are talking about thousands or more items at a time.

[bcooney]

I have invoice lines and each invoice line can be assigned to an employee for commission.

So, each invoice line item record has an employeeID? Or, can each invoice line item be considered for commission to more than one employee?

I run a report of all the invoice lines that are attached to invoices that have no balance ( the customer has paid therefore I can pay commission ).

I'd probably create a flag_CommissionDue to easily find those Invoice line items.

I then make a record in my Paid commission table that has a foreign multi-key of the invoice line PK's I give the paid commission record a total amount paid in commission and now those lines show being paid.

Although mk will work, I'd prefer a join table between Payment and Invoice Line Item.

The reason I assign commission per invoice line is so that one employee can get paid for up-selling and another can get paid for doing an onsite related to the same invoice.

So, any one invoice has line items with different Employee IDs and each invoice line item has only one Employee ID?

[Eden Morris]

Q: So, each invoice line item record has an employeeID? Or, can each invoice line item be considered for commission to more than one employee?

A: One Employee per line item

Q: I'd probably create a flag_CommissionDue to easily find those Invoice line items.

A: right now I have a find records script step, one less field to look at.

Q: Although mk will work, I'd prefer a join table between Payment and Invoice Line Item.

A: I get tired of making more tables

Q: So, any one invoice has line items with different Employee IDs and each invoice line item has only one Employee ID?

A: Each invoice is assigned to the employee that made the invoice. Any commission is assigned to that employee. If another employee does something that will incur commission they can have that invoice line assigned to them and the rest of the invoice is still assigned to the creator. For example one emp upsells and another delivers the product to their house instead of customer pickup.

[bcooney]

However, the join table will come in handy for any reporting needs. Also, a flag field consolidates your logic in one place.