Multiple Child TOs or a script.

[Musoguy]

Hi folks,

 

So I have a conundrum! I am trying to create a field that will give me the total from the same line number in three separate portals. Trouble is the user can custom sort each of the portals with up and down arrows in each line which through a script changes a sort order field in each record. There are 16 records in each portal. My conundrum is do I create 16 constants and relate them to the sort order field in three child TOs (one for each portal) to get the sum of each line which leads to a lot of added fields and a ton of TOs, or do I do it through a script attached to a script trigger? My gut tells me to go with a script. Wondering if anyone thinks that this is the wrong route to take, or if indeed there is a better solution that I am not thinking of.

 

James

[Ocean West]

james

 

can you give me an example of the data in the 3 portals - i assume a field with data (text ) and a field with an amount or quantity ?

[Musoguy]

Hi Stephen, thanks for the reply. The portals in question just have an amount field in each. They correspond to three other portals that have people's names in them taken from a related table. The sort order field is in another related table which is where the portal records are shown from. All three tables are related by a "employee_ID" key (had to do it this way as the amount field should only be available to see if the user is an admin)

I forgot to say originally, there are actually three sort fields in the same table, one for each portal

[Ocean West]

are the three "portals" based on 3 different tables, or the same data in the same table?

[Musoguy]

Same table, same TO. Filtered differently to cut down on TOs a little. And a different field in each portal to match up with a different record in each of the name portals. I don't think I am explaining this very well! The database is a summer theatre program. This particular layout shows the job type each employee will be doing for each session of which there are three (one portal for each session - six total - Session 1, 2 and 3 employee names and session 1, 2 and 3 amount paid). It is basically an overview table that the client insisted had to look like their original excel table (which is always fun!) So there are six portals horizontally across the page. The portal I need to add to the right of these needs to show a field that gets the sum of the three amounts for the same portal lines in the amount portals which may well each come from different records depending on the sort order, but the sort order number will always be the same for each line. Hope that makes a little more sense!

[Ocean West]

i think i understand ---

 

you want to compute the total amount for a given employee across all the sessions a "summary total"

 

there are a few ways this can be done - using a scripted method - to generate a summary of totals

or you could even use Execute SQL to do this but since the data is all coming from the same table the scripted option would prob be best

you may need to have a trigger run to make sure that its always up dated. 

 

checking resources - brb

[Musoguy]

Thank you. You are almost correct, however it is not by employee it is by portal line. Each line of the 3 portals may well have 3 separate employees in it. Each line is a different Job description (think I forgot to mention that!) This is what is throwing me a little!

[eos]

– the records are in those portals because they have a key in common

 

– in addition, each set of three records has the same ordering number

 

Using these two bits of data, either create a self-join relationship and sum up the related records, or do it via SQL.

[Ocean West]

Not sure this is on the right track but here is one method.

 

 

sessionTotals.fmp12.zip

[Musoguy]

Thank you both so much for your help. What I ended up doing was along the lines of what eos said. The only difference is instead of a self join I created a new table with 12 records. A sort field with entered data in each record 1 through 12 which I then related to the Session Sort fields in three TOs and a total field. Just did it this way as it gives me control over who can see the total field.