I have a very odd download of data from the backend of a bookings website that I need to process. It captures the person data and what they purchased. But if there are multiple people on the one purchase, it assigns the group total to every person, not the proportional total. So I get;

 

ID, Number of people, Person, Amount

 

201  1 Joe $100

202  1 Mary $100

203  1 Zac $400

203  2 Paul $400

203  3 Sandy $400

203  4 Elise $400

 

I can do a sort based on ID, so technically I could do a summary count. So I could do something like:

 

Sort records, count records in groups, divide Amount by Count and paste the result into a field 'Real Amount'

 

How would I script this so it does the count and pastes the result into another field?

 

 

You can do it with a script, but a faster way might be to create a temporary self-join with ID = ID, so each record can see the Max ( selfJoin::NumberOfPeople ) for its ID group, and use that value e.g. with Replace Field Contents for realAmount.

I have a very odd download of data from the backend of a bookings website that I need to process. 

 

If you wish to create a self-join based upon that TempID, I would suggest similar to Eos except I would use the following instead of Max():

 

SelfJoin::FullAmount / Count ( SelfJoin::TempID )

 

ADDED:  I think number of people can easily be obtained from counting the TempID so we don't need Max().  I might be wrong in my understanding of Bob's need however ... 

 

But you do not need to add another table occurrence, Bob, since script can handle it with a bit less footprint and speed should be the same since you must walk the records to *set a value on each record regardless.  You will need a summary field sCountIDs (any field can be used that is never empty, usually PrimaryKey of the table is best).  So if you've created a singleAmount field (number) then script would be:

 

Freeze Window

View As [ Form ]

Show All Records

Sort Records [  sort ascending by the temp ID (201, 202 etc) ; no dialog ]

Go To Record/Request/page [ first ]

Set Error Capture [ On ]

Loop

Set Field [ table::singleAmount ; GetSummary ( sCountIDs ; primaryKey ]  <-- Bob, notice I use Set Field[] instead of paste - it is much more dependable

Go To Record/Request/Page [ exit after last ; next ]

End Loop

 

* Note that this does not include protection from multi-user if someone is changing one of these records.  I've set error capture ON so it will run clean through the records and not stop so run this when Users can't potentially lock any of the records.

Edited August 4, 201312 yr by LaRetta

I'm missing something as I'm trying these solutions. I've created a self-join based on the ID in my example but nothing is appearing in the fields. It may be because I can't follow the shorthand of the answers, and I'm a bit all thumbs when it comes to relationships. Anyone have the time to exand one of the examples above? Thanks.

Try this. I wasn't in the mood to write a script, so I went with my idea. You can continue using that field, or, if you need an indexed version, define a new field and use the logic to do a Replace Field Contents (which you may want to put in a script for further use).

SelfJoin_eos.fp7.zip

Thanks eos - that's great and I appreciate the time it took you. It'll save me a lot of hassle and I'll see how it is in action.