Scripting between two different files into the same record

[lizzie]

Greetings

I hope I can describe this correctly...

I want to (and am in the process of) creating a script to analyse Sales Consultants billings to earnings. For this I have to run scripts in two different tables: the Invoices Table (to get the sales totals per sales consultant) and then the Salaries Table (to get the salaries and commissions for that particular Sales Consultant).

I start in a Table called "Scorecard" and then go to Invoices and run a script. I then set the required fields to globals, go to a layout in Scorecard and post this data into the correct fields. I then leave Scorecard again, go to Salaries, run a script to find the correct salary slip for that employee for the month, set the required fields to globals and return to the layout in Scorecard.

This is where my script "breaks". I have to select the same record as before and post the data into the correct fields within the same record as the data was posted for the data from Invoices.

How do I correct this? I stipulated that the script must go to the last record and post the data there but it fails to do so...

I hope I am making sense!

Any help would be greatly appreciated.

[LaRetta]

Hi Monsta,

What type of output is required here? It seems that you can just display what you wish directly on the Sales Consultant layout (via portals). If you have relationship from Sales Consultant to Invoices table based upon Sales ConsultantID and also to Salaries table (again on Sales ConsultantID) it would work fine. You would also want to include globals to filter the relationships down to the specific period you required.

Even if you don't want to display via portal, you still have the proper relationships (it sounds like) to produce your totals using both, as:

Total Billings = Sum ( FilteredInvoicesTable::Dollars )

Total Salaries = Sum ( FilteredSalariesTable::Dollars )

Anyway, I can't give specifics without seeing your structure or knowing how you need to present the results. You might also consider a relationship between Billings and Salaries (on Sales Consultant) and producing a report from perspective of either of those tables (whichever is the 'many' side). I would certainly consider these approaches over hopping around writing data because I doubt it is necessary. That doesn't mean it may not be; depends upon whether you want to display the data, produce a report, export and so forth. Even if you MUST write data, use a relationship and write the data through that instead of using globals to hold all the fields' information.

LaRetta

[lizzie]

Hi LaRetta

Thank you so much for the advice. I am really flinging myself into the deep-end with FM and find I do get "caught" in a mode of thinking.

The reason I chose to use globals was because The Sales Scorecard which measures Sales Performance is supposed to take a snapshot in time and the hopping to and fro through the setting of globals was the one way I knew how to achieve this.

I am doing much reading up on FM and find portals quite finicky at this point in time. My problem can almost always be pinned down to a faulty relationship! But after seeing the video: "Graph Rules" by Matt Petrowsky, I have realised how powerful portals can be... I think it's just a matter of practice and time!

Thanks again for your advice! It's very much appreciated!

[LaRetta]

So two questions still remain:

1) What kind of display of the data is needed here and 2) did I help you solve your problem at all or do you still have it? If so, I and/or others can help you using different approaches if you are uncomfortable with the suggestions. If you would wish to provide a file for us, I would be happy to show you a few ideas. Otherwise, we await your direction ... :wink2: