Multiple Loops Script

[Miss A!]

Hi there - Miss A! here      Help please....

 

I have a script that searches through records of vehicle invoices, i group records(using summary field) according to registration number and i get the first record's KMreading field and insert it on KMStart field on Vehicles running cost layout for the corresponding Vehicle reg number record, i get last record of the group and take the KMReading field and insert into the KMEnd field in Vehicle Running cost layout - i loop through until last record and script serves this task well.........

 

OK, now i want to go back to the invoice records, this time i want to search for a particular ledger account, group the records and SUM THE AMOUNT field for each particular group of records(mini found set)........This i am failing to get a script for, so i have attached script for how far i have gotten so if i may please you help me from there - many thanx.

 

 

LG

Miss A!

Vehicle_Invoice.pdf

[Lee Smith]

Why are you posting to the General Topic for FileMaker version 13 when you are using version 12?

If you are now using v13 let me know, or I will need to move this to a proper topic area.

 

Also, a copy of your FileMaker file, or a mockup of it would be more helpful to us than the PDF in helping you.

[bruceR]

And don't forget that the file can be a clone.

 

Remember that you are trying to make it easy for other people to help you.

 

Saying        does not actually accomplish that.

 

Providing useful detail and description, providing files, learning database terminology and FileMaker terminology are the things that matter.

[Miss A!]

Hi,

 

i am on FM13 Advanced, please do not move the topic  - I have been posting on this platform as FM13 user and it displayed so on my profile, i do not understand how it has changed to Ver.12........File is too large to upload.

 

 

bruceR - i am trying.

[Lee Smith]

Change your profile to reflect your current information. Here is a quick link for you . MY PROFILE

[Lee Smith]

.File is too large to upload.

 

It is explained in this  how to attach a file instructions. Just follow the steps and if you have any questions regarding the steps come back and post here. ATTACHED FILE

[LaRetta]

File is too large to upload.

 

Hi Miss A,

 

I understand the file is too large to load but I've read your script multiple times and it is not clear to me even what you are trying to accomplish.  I am sure there are much easier ways to achieve the results you wish, if you are structured properly.  I also see you are attempting to use 'smart summaries' so you've been studying (and obviously trying).  :-)

 

We need you to create a simple file with just the pertinent relationships and then brief description of what you are trying to accomplish (in human-speak).  In all my years in this business, I do not believe I've ever had to jump to other tables to loop and gather data as you are doing.  It is highly likely that you can use a relationship to identify the values you wish or use ExecuteSQL() to get 'er done.  But I have no concept as to why you are going through this routine so we need more help to help you.

[bruceR]

"I am trying."

OK.

What is the purpose of the script you attached here?

For example; you want to go back to the invoice records.

Why? Because the invoice design is not actually correct and complete?

Because the invoice design is correct and complete but you need to provide supporting detail?

Because "an invoice needs to provide the following information:" then upload an example invoice. It should show the detail you require.

You might have to entirely fake an invoice to do this, create it in Word or something; just to indicate what is required.

 

As mentioned by LaRetta; nobody can understand what you are trying to accomplish.

[Miss A!]

Ok guys, thank you  very much for your good willingness........I managed to put together something to give a picture of my situation, i will try explaining again:

 

The first part of the script is working(like i expect) - with the part i have commented out, i am struggling(and trying) to create another loop(not possible??), this time i am searching(Remmitance_Transport) for the records whose ledger account is "1030 Tyre New" OR "1031 Tyre Repairs" and  Date range still between the same range specified in the first part of the script. Now with this set, i should sum the Amount_paid_to_creditor for each vehicle and return this value to the Tyres columnfield on vehicle running cost layout for the specific vehicle matching on Registration Number. I would like to do this for the other fields(Repairs, AC, Service) and the last column on vehicle running cost is the sum of the values in (Repairs, AC, Service and Tyres) fields for each vehicle - attached file(Username: Saima Malapi Password: saima).

 

*i am not sure i am being clear enough but i can answer further questions*

[Miss A!]

Am afraid my file is failing to attach - file size is 35 MB now.

[LaRetta]

Files must be zipped before they can be attached.  And once you attach your file be sure to click the button right below it called 'attach'.  :-)

 

Also you are telling us what you want the script to do but please also tell us why you are doing this?  Please provide a screen shot with the result you want (as Bruce says even if in a word document).  We need to understand the purpose behind it.  It is possible that the file will show us but from your description, I think we still need to understand the reasoning behind it.  

[Lee Smith]

Oh come on Miss  A, Did you read the information provided at the link I posted? 

 

The information provided there explains some of the alternatives available for posting a file which includes that the file must be ZIPPED. 

 

If you are having troubles in some way contact me by Private Message.

 

Lee

[Miss A!]

Oyeeaaah, here it is - managed to reduce it even further, now at 1.8MB, help me please and thanx loads!

TransportRemittance Copy.fmp12.zip

[bruceR]

 Please answer the questions. 

Where is the example invoice?

What are you trying to accomplish?

[LaRetta]

Hi Miss A,

 

I believe that Vehicle Running Costs should not be cleared and re-entered; in fact regardless of anything else, they should not be cleared and reentered when their values already exists elsewhere.  That is the purpose of relational.  The Vehicle Running Costs should be individual records in a table whereby they are left for history (and summary) purposes.  

 

But like Bruce, I still am unclear what you are trying to accomplish.  Can you tell us what each table represents?  It seems that Remittance_Transport holds fields which really belong to a Vehicles table so something still feels off.   :-)

[bruceR]

Everybody here makes their own judgement about how much free help they are willing to provide, and what level of effort and organization they prefer to see from the person posting the question.

 

For me, this file requires too much. It is a complex, hard to understand solution, containing both data structure problems and language problems. It needs a lot of redesign by an experienced developer and I recommend you find a way to pay for professional design help.

[LaRetta]

Surprisingly (smile), I completely agree.  The base structure is the most critical ... get it wrong and the rest of your work is difficult and many times you will hit a complete dead-end or you will have to remove large chunks of your work and begin again.  If the structure is correct, the rest is much easier AND you can receive free assistance because the base is relationally sound so the professional developers here who assist will be able to provide spot-on assistance.

 

A normalized base is worth its weight in gold and NOTHING should be build into a file until a relationally-solid preliminary base structure is decided (and hopefully reviewed and approved by someone with relational theory or posted here for review).  If you throw sticks on the ground and then begin to build your house on top, you will regret it.  Learn how to pour concrete FIRST before you build or ask an expert to pour it for you.

 

Miss A, in post 11 I asked you to explain more for us and you never did.  I ask you again.  I've spent easily more than four hours of my time trying to understand your need.  You need to realise that we do not know your business and it is your responsibility to explain it to us.  Pretend we are children or a new employee and you are explaining what the tables do and why they are structured as they are.

 

Make sense?  :-)

[Miss A!]

What i would like for the script to do, is get the sum of the Total paid(Amo_Credit_Total field) for each particular vehicle for a particular Ledger account from the Remmitance layout for which the Date(Date_Invoice) is between the Start Date and End Date fields on the Vehicle Running Cost Layout. The Ledger accounts of interest are:

 

• 1030 Tyre New and 1031 Tyre Repairs -->> The sum amount(Amo_Credit_Total) for records with this two ledger accounts must then be populated onto the Vehicle running cost Tyres Column for that vehice, 
• 1010 Service -->> Sum amount(Amo_Credit_Total) for this records add to Vehicle Running cost layout for the Service column for that vehicle
• 1011 Repairs and Stanic Repairs  -->> Sum amount (Amo_Credit_Total) should go to Repairs Column for that particular vehocle
• 1012 Air Con -->>Record Sum for this records should go to the A/C column in Vehicle Running Cost.
• The total field at the end in vehicle running cost should sum repairs, Service, Tyre and A/C values across for each vehicle.......

So the first two columns in Vehicle Running Cost are static(Vehicle Name and Reg Nr) and for the records returned from remittance match the Second partword of Vehicle field in Remittance layout to the Reg Nr field in vehicle running cost - thereby updates the amount sum.

 

Purpose, on the initial part of the script, is to get the KM reading at the beginning and End of the specified dates for each vehicle and then also to find total costs on Tyres, Repairs, Service and AC for each vehicle........and all this information is in Remmittance Ordinary and i would like to display it on Vehicle Running Cost Layout.

[LaRetta]

.......and all this information is in Remmittance Ordinary and i would like to display it on Vehicle Running Cost Layout.

 

Hi Miss A,

 

You are using repeating fields for data.  it is highly discouraged for this very reason and it is particularly problematic in financial tables where you need to group and summarise.  Your amounts are repetitions but you want a report per vehicle of its costs per date range.

 

If you were using a normalised structure, you already have all the information you need in Remittance Ordinary, sub-summarised by Vehicle and Ledger Account, first performing a find based upon user-entered startDate and endDate globals and then sorting the results.  An improper structure causes you AT MINIMUM 50-fold the work and slows your solution to dawg-speed (because of the bloated fields and calculations required to support it).

 

I know this has been mentioned to you before and I regret telling you this but the longer you wait to change it ... the more needless work you'll have put into it which has to be removed.  If it is critical that you finish this report before you can rebuild your solution then I would suggest you hire someone to do it for you using a virtual table (and make sure the person is a Repetition Master as well).  I'm afraid I don't have a chunk of time to handle it, free or otherwise, right now and I'm no Repetition Master.  

 

Maybe someone else can take it on.  Sorry.

[LaRetta]

By the way, if you were willing to consider restructuring pieces of your solution (on the backside; nothing noticeable to users or functionality) then I would try to answer questions here as you work through it, and I'm sure others would as well.

 

So you could keep using your existing solution and solve critical design needs while reviewing structure changes for a more-relational concept.  Many of these changes are easier than you think and can be developed on the side and then the data migrated in an evening.  Some changes can be performed without interruption even.  Users wouldn't notice it except it would become snappier.  :-)

 

This forum is a learning pot. You can dip in as often as you need.  You needn't feel alone in it.  And I'll try to solve your current issue as well when I get the chance but it would be a few days out.  Geez.  LOL.

[Miss A!]

Seems only myself understand my problem and the solution i want for it and the only thing stopping me is my little to non-existence programming background, i am sure i have the picture and it is not that very complex only i am struggling to put it out there for others to understand it..........I will keep pressing, i thank you a lot for your willing and good efforts - lost but i am not stopping

[bruceR]

If that is your conclusion, you are not paying attention.

No, you do not have the picture.

Your solution needs to be redesigned.

 

Re-read the replies, particularly LaRetta's replies.

[Miss A!]

My conclusion is that we do not understand each other - basically due to my low level knowledge and understanding of filemaker and probably databases in general, i am unable to put it to you in a way that can help you to help me........I don't even want to get to the topic of redesigning because i got nothing on that as what i am looking at on the Remmittance layout is the info in the fields Vehicle, Ledger Account and Date Invoice and Amount Paid for that information in those fields i would like to sum the Amount field for each vehicle for each ledger account and display that summed value on the vehicle running cost layout(Reason behind is to see how much is spent on each vehicle between two given dates, so this information is suppose to be changing depending on the date range specified thats is why i need the layout to be cleared every time a new date range is specified) - so this is the picture i am talking about for which implementation i am failing badly.........

 

I do feel all of you expert's WILL to assist(which i highly appreciate) and am sorry i am causing(and have caused) you all this exhaustion(i have exhausted myself in the process as well) - So i take it sadly that due to my current file's design i will not be able to get the solution i am aiming for  

[LaRetta]

Hey there, Miss A,

 

A few critical questions (in bold) need to be answered and I do not care how long you make your response, only insufficient response would irritate me so please speak (type) freely:

 

In Remittance Transport, why do the following fields have 15 repetitions?

 

Amount_Credit_Incl_Vat ... auto-enter = Erweitern(Betrag)

Amount_VAT_Exempt 

Amount_VAT_Input 

Date_Invoice

Reference

Your Invoice

 

… but these next fields only have 2 repetitions?

 

Amount paid to Creditor ... auto-enter = Amount_VAT_Exempt + Amount_Credit_Incl_Vat

Amount_Price_Total ... auto-enter = Quantity * Amount_Price Unit

Amount_Price Unit

Quantity

 

What is the purpose of Remittance_Transport? 

Many of your table occurrences (and fields) in your graph are in German so I cannot grasp the concept of the information flow (and yes I was too lazy to Google Translate them) and, since the fields are a bit misplaced and overlapping, I need a clear vision of what a single record in this table represents. 

 

What is the purpose of Vehicle Running Cost?

Once you have the figures in Vehicle Running Cost, what will you do with the results?  Will it be printed, emailed, PDF? Who will it be given to and why?

 

Above all, please do not bother explaining again how you think it should be solved and how you need this figure to be put there - you've already explained all of that.  :-)

[LaRetta]

Also, is it true that one Vehicle (Vehicle Transport) can have many Remittance Transports?  You have both IDs in both tables.  Vehicle Transport appears to be a table which holds one record for each vehicle.  Is this correct?

[bruceR]

LaRetta, more power to you if you are willing to do the extended work of trying to help here.

It is a long, difficult job.

 

But it seems to me there isn't a need to call yourself lazy, when you decided not to add even more free help (translating an idiosyncratic FileMaker design from German to English) to what you are already offering.

[LaRetta]

Hi Bruce,

 

I AM lazy when I know I can Google Translate and get answers but laziness isn't all bad – it makes for efficient code. :-)

 

If Miss A chooses to answer my questions, I may not need to know the name of every German field or table name but I certainly need to understand the repetitions, purpose of the tables and cardinality before I decide if I can help her with that report.  It may require more time/effort than I can give right now.  It might also provide enough information that someone else might want to take it on.  

 

I'm just not ready to give up yet.

[Miss A!]

Hi LaRetta,

 

Sorry - i had a weekend, could not pop in.....

 

Well to start with, the whole FM solution was created by some run-away programmer, i will never get hold of

 

So, I really do not know what is the purpose of all that repetition and for the case that only the first repetition is made use of "no less".

A record represents information on an invoice for a particular vehicle: Code is for the businessdealer and Company is businessdealer in full, the ledger account that represents the particular service or action we are paying for, the invoice date, payment date, Vehicle  registration number(unique for each vehicle), KM Reading on the vehicle at the time of invoicing,  amount paid to creditor, amount paid and total paid, entry date, modification date - So the purpose of Remittance transport is to record payments on servicesbusiness conducted on the vehicle.

 

In Vehicle Running Cost, i want to display the Kilometer readings on the vehicle on the start date and end date of a given range, also i want to see how much is spent on a particular vehicle given a date range for each ledger account for each vehicle.

 

And Yes, One Vehicle has many Remmitance Transports, Vehicle is holding only one record for each vehicle.

[LaRetta]

I am sorry, Miss A.  I began to rewrite this process for you (using virtual technique) and there were just too many issues to split apart, with my limited time availability.  I even began looking up the field names which were in German but there were fields such as lxn_, lfn_.  Shortcuts in field names is always a huge problem (and a personal pet peeve).  If another developer needs to work, it is impossible for them to decipher.

 

In a relational structure, you should have been able to generate a sub-summary report in Remittance_Transport - end of story - and very simple.  However, you have charges as fields and wanted to also group certain charges (such as Tyres).  If you wish to present a SIMPLE file with only a single table (Remittance_Transport is all that is needed) with ONLY those critical fields to total and in English, I will still provide a script which loops those records, writes to global variables and produces the report using virtual technique.  You do NOT want to use regular records (as you were doing) because if someone else runs the report, it will overwrite the first User's values.

 

If you do not wish to provide a simple file with only sample data and only the fields you need for your report so I can work on it then I again suggest that you hire someone to restructure you.  Otherwise, you'll just keep hitting these kinds of brick walls at every turn.

[Miss A!]

Happy New Year wishes for us all here - Up the Forum!!!

 

I would like to know if there is a possibility in achieving a scripting task with multiple loops(Unfortunately i do not have a file where am pulling my scenario to share/upload but will try a crystal explanation why i want to do so)

 

I am searching the database on different found sets whereby each particular found result i loop through those records and update another layout on which a particular entity(A Vehicle identified by Reg Number) is appearing only once. For the first loop on Vehicle Reg Number, i am updating the first and last KM Reading for each vehicle. I would like to go on and then again create a second found set this time based on the Vehicle and a Particular GL Account, for this i would like to sum Amount for records of a particular vehicle in the found set and update on the Vehicles layout for that Vehicle and loop through for all vehicles updating. I will go on with more loops for different found set on different Ledger accounts and update accordingly the sum amount recorded for each vehicle.

 

I would like to know if this is possible and more importantly, i would like to know how do i Sum records in a particular found set by specific groupings of records such that for a found set of different vehicles,  Sum the amount field for a specific vehicle category/grouping by Reg Number, that is in a found set say of 12, Sum the Amount field for all Records belonging to a particular vehicle Reg Number N1667W.....and so on for all Vehicles Found.

 

 

Sorry i am unable to share an example file - i just hope my explanation is close to the point......

 

 

[Miss A!]

Hi all, finally got the hang for the script to my solution

Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ] Perform Script [ “Clear Vehicle Cost” ]
Set Variable [ $$SDate; Value:Vehicle_Running_Cost::lxd_DateS ] Set Variable [ $$EDate; Value:Vehicle_Running_Cost::lxd_DateE ] Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ] Show All Records
Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Date_Invoice:; $$SDate&"..."&$$EDate ]
[ Select ]
Set Field [ Remittance_Transport::KM_Reading; "*" ] Set Error Capture [ On ]
Perform Find [ ]
If [ Get(LastError)=401 ]
Show Custom Dialog [ Title: "Alert"; Message: "Sorry, No records between " &$$SDate&" and " & $$EDate & ". Please specify a different range."; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ] Go to Field [ Vehicle_Running_Cost::lxd_DateS ]
[ Select/perform ] Exit Script [ ]
End If
Sort Records [ Keep records in sorted order; Specified Sort Order: Remittance_Transport::Vehicle; ascending Remittance_Transport::Date_Invoice:; ascending ]
[ Restore; No dialog ]
Go to Record/Request/Page
[ First ] Loop
Set Variable [ $VReg; Value:MiddleWords ( Remittance_Transport::Vehicle ; 2 ; 1 ) ]
Set Variable [ $Count; Value:GetSummary ( Remittance_Transport::xs_TotalVehicle_Count ; Remittance_Transport::
Vehicle ) ]
Set Variable [ $Next; Value:Get ( RecordNumber ) + $Count ]
Set Variable [ $KMStart; Value:Remittance_Transport::KM_Reading ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Vehicle_Running_Cost::__ID_Vehicle: “$Vreg” ] [ Restore ]
Set Field [ Vehicle_Running_Cost::lxn_KMStart; $KMStart ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Go to Record/Request/Page [ $Next - 1 ] [ No dialog ]
Set Variable [ $KMEnd; Value:Remittance_Transport::KM_Reading ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Vehicle_Running_Cost::__ID_Vehicle: “$Vreg” ] [ Restore ]
Set Field [ Vehicle_Running_Cost::lxn_KMEnd; $KMEnd ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Go to Record/Request/Page
[ Next; Exit after last ]
Exit Loop If [ $Next > Get ( FoundCount ) ]
End Loop
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ] Show All Records
#
#
#
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ] Show All Records
Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Ledger Accounts; "1030 Tyre New" ] [ Select ]
Perform Find [ ]
Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Date_Invoice:; $$SDate &"..."& $$EDate ] [ Select ]
Insert Calculated Result [ Remittance_Transport::Ledger Accounts; "1031 Tyre Repairs" ] [ Select ]
Extend Found Set [ ]
Sort Records [ Keep records in sorted order; Specified Sort Order: Remittance_Transport::Vehicle; ascending ]
[ Restore; No dialog ] Go to Record/Request/Page
[ First ] Loop
Set Variable [ $Count; Value:GetSummary ( Remittance_Transport::xs_TotalVehicle_Count ; Remittance_Transport:: Vehicle ) ]
Set Variable [ $VReg; Value:MiddleWords ( Remittance_Transport::Vehicle ; 2 ; 1 ) ]
Set Variable [ $Next; Value:Get ( RecordNumber ) + $Count ]
Set Variable [ $$Total; Value:GetSummary ( Remittance_Transport::xs_TotalTyres ; Remittance_Transport::Vehicle ) ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Vehicle_Running_Cost::__ID_Vehicle: “$Vreg” ] [ Restore ]
Set Field [ Vehicle_Running_Cost::lcn_Tyres; $$Total ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Go to Record/Request/Page [ $Next ] [ No dialog ]
Exit Loop If [ $Next > Get ( FoundCount ) ] End Loop
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ] 
#
#
#
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Show All Records
Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Date_Invoice:; $$SDate&"..."&$$EDate ] [ Select ]
Insert Calculated Result [ Remittance_Transport::Ledger Accounts; "1010 Service" ] [ Select ]
Perform Find [ ]
Sort Records [ Keep records in sorted order; Specified Sort Order: Remittance_Transport::Vehicle; ascending ]
[ Restore; No dialog ] Go to Record/Request/Page
[ First ] Loop
Set Variable [ $Count; Value:GetSummary ( Remittance_Transport::xs_TotalVehicle_Count ; Remittance_Transport:: Vehicle ) ]
Set Variable [ $VReg; Value:MiddleWords ( Remittance_Transport::Vehicle ; 2 ; 1 ) ]
Set Variable [ $Next; Value:Get ( RecordNumber ) + $Count ]
Set Variable [ $$Total; Value:GetSummary ( Remittance_Transport::xs_TotalService ; Remittance_Transport::Vehicle ) ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Vehicle_Running_Cost::__ID_Vehicle: “$Vreg” ] [ Restore ]
Set Field [ Vehicle_Running_Cost::lcn_Service; $$Total ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Go to Record/Request/Page [ $Next ] [ No dialog ]
End Loop
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ]
#
#
#
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Show All Records
Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Date_Invoice:; $$SDate&"..."&$$EDate ] [ Select ]
Insert Calculated Result [ Remittance_Transport::Ledger Accounts; "1011 Repairs" ] [ Select ]
Perform Find [ ]
Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Date_Invoice:; $$SDate &"..."& $$EDate ] [ Select ]
Insert Calculated Result [ Remittance_Transport::Ledger Accounts; "9011 Stanic Repairs" ] [ Select ]
Extend Found Set [ ]
Sort Records [ Keep records in sorted order; Specified Sort Order: Remittance_Transport::Vehicle; ascending ]
[ Restore; No dialog ] Go to Record/Request/Page
[ First ] Loop
Set Variable [ $Count; Value:GetSummary ( Remittance_Transport::xs_TotalVehicle_Count ; Remittance_Transport:: Vehicle ) ]
Set Variable [ $VReg; Value:MiddleWords ( Remittance_Transport::Vehicle ; 2 ; 1 ) ]
Set Variable [ $Next; Value:Get ( RecordNumber ) + $Count ]
Set Variable [ $$Total; Value:GetSummary ( Remittance_Transport::xs_TotalRepair ; Remittance_Transport::Vehicle ) ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Vehicle_Running_Cost::__ID_Vehicle: “$Vreg” ] [ Restore ]
Set Field [ Vehicle_Running_Cost::lcn_Repairs; $$Total ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Go to Record/Request/Page [ $Next ] [ No dialog ]
Exit Loop If [ $Next > Get ( FoundCount ) ] End Loop
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ] 
#
#
#
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Show All Records
Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Date_Invoice:; $$SDate&"..."&$$EDate ] [ Select ]
Insert Calculated Result [ Remittance_Transport::Ledger Accounts; "1012 Air Con" ] [ Select ]
Perform Find [ ]
Sort Records [ Keep records in sorted order; Specified Sort Order: Remittance_Transport::Vehicle; ascending ]
[ Restore; No dialog ] Go to Record/Request/Page
[ First ] Loop
Set Variable [ $Count; Value:GetSummary ( Remittance_Transport::xs_TotalVehicle_Count ; Remittance_Transport:: Vehicle ) ]
Set Variable [ $Next; Value:Get ( RecordNumber ) + $Count ]
Set Variable [ $$Total; Value:GetSummary ( Remittance_Transport::xs_TotalRepair ; Remittance_Transport::Vehicle ) ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Vehicle_Running_Cost::__ID_Vehicle: “$Vreg” ] [ Restore ]
Set Field [ Vehicle_Running_Cost::lcn_AC; $$Total ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Go to Record/Request/Page [ $Next ] [ No dialog ]
Exit Loop If [ $Next > Get ( FoundCount ) ] End Loop
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ] Show All Records
#Script By MSA

[Wim Decorte]

What's the logic behind the script?  You're building a bunch of found sets in different layouts, but the user can really only look at one layout.  So why spend all the time building all these found sets?

 

I managed to put it all together, it is working all good for me but since am still not so there on performance optimisation, am sharing as i would like your opinions of/on it embrace yourself, its a long one........

Set Variable [ $$SDate; Value:Vehicle_Running_Cost::lxd_DateS ] Set Variable [ $$EDate; Value:Vehicle_Running_Cost::lxd_DateE ] 

 

Don't use global variables if you don't need to.  Use local variables.

 

I managed to put it all together, it is working all good for me but since am still not so there on performance optimisation, am sharing as i would like your opinions of/on it embrace yourself, its a long one........

Enter Find Mode [ ]
Insert Calculated Result [ Remittance_Transport::Date_Invoice:; $$SDate&"..."&$$EDate ]
[ Select ]
Set Field [ Remittance_Transport::KM_Reading; "*" ] Set Error Capture [ On ]
Perform Find [ ]

 

No need for "insert calculated result"; you can use Set Field there too.

The second request in there ("*") does nothing, so skip it.  You're basically saying "I don't care what values are in that field", so it does not add anything to the find request.

 

I managed to put it all together, it is working all good for me but since am still not so there on performance optimisation, am sharing as i would like your opinions of/on it embrace yourself, its a long one........

Perform Find [ ]
If [ Get(LastError)=401 ]
Show Custom Dialog [ Title: "Alert"; Message: "Sorry, No records between " &$$SDate&" and " & $$EDate & ". Please specify a different range."; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
Go to Layout [ “Vehicle_Running_Cost” (Vehicle_Running_Cost) ] 

 

None of the user's actions on the custom dialog will actually stop the script because you are not handling the user's choice with get(lastmessagechoice).

The script will continue regardless of what the user clicks.

Inside you loop (and I only looked at the first section), you do a find.  That find negates the very first find in your script.  So that first find is meaningless.

 

I think you meant to do a constrain find inside the loop?

[Miss A!]

Thank you so much for your feedback, 

What's the logic behind the script?  You're building a bunch of found sets in different layouts, but the user can really only look at one layout.  So why spend all the time building all these found sets?

Well, thing is i got a layout(Vehicle Running Cost) with static fields for Vehicle names and Registration numbers, i got 4 more fields which need to be populated by the script with records from Remmitance_Ordinary layout. For the bunch of found sets, that is because i got different search criteria(except the date range is the same for all set of searches) for each field i need to populate on the Vehicle Running Cost Layout.

 

 

 

Don't use global variables if you don't need to.  Use local variables.

 

No need for "insert calculated result"; you can use Set Field there too.

The second request in there ("*") does nothing, so skip it.  You're basically saying "I don't care what values are in that field", so it does not add anything to the find request.

I am using global variables for the Date range only, because it is suppose to be the same for all my search criteria sets. 

I am going to re-do with Set Field instead of Insert Calculated Result script step(In my not so well knowledgeable idea, i always thought i go for insert calculated result step when dealing it on variables be it local or global) Thanks for that correction.......on the second request i am specifying "*" to include records for which there is a KMReading entry because for some records the field KMReading is empty and i do not need them, basically KMReading is a number field and not text.

 

None of the user's actions on the custom dialog will actually stop the script because you are not handling the user's choice with get(lastmessagechoice).

The script will continue regardless of what the user clicks.

Will take another look on the error handling case and BTW i am going to need error handling for all sets of searches i have created therefore am thinking instead of the Exit Script step i should do only with the End IF so that the script goes on to perform the remainder of the other search criteria sets.

 

Inside you loop (and I only looked at the first section), you do a find.  That find negates the very first find in your script.  So that first find is meaningless.

 

I think you meant to do a constrain find inside the loop?

The first part of the script before the Loop is on a different layout(Remittance_Ordinary) that finds all records between the date range and for which there is a value for the KMReading field then the Error handling part(That which you mentioned is a fail) then sorting and gong to the first record on that layout.........Inside the loop i define the variables and go to the Vehicle Running Cost Layout where i search for the vehicle that belongs to the  current record i am on(from the Remmittance_Ordinary Layout) and set the Beginning KMReading(lxm_KMStart) for that vehicle to the Value held by $KMStart variable, goes back to Remittance_Ordinary Layout and go to the last record for the set of records for that particular vehicle, define value for the Variable $KMEnd on KMReading field Returns to Vehicle Running Cost, search for that vehicle again  and update lxn_KMEnd Field.

 

 

I have attached snapshot for the layouts, the fields with colour fills are empty, meaning no matching entries on the Remittance_Ordinary layout, my idea was to display a zero for empty fields but i am stuck on it.......would like some help on that if its a doable exercise.

 

Ohh, was also thinking - Will creating search criteria sets individually(as separate script files) and using the perform script step make for good performance?

 

Thank you very much for your very good efforts, will take myself through again......am so glad i got somewhere after more than a month trying to give up, this has given me heart 

 

PS:I noticed prefixes for the fields lxn_KMStart and lxn_KMEnd suppose to be lcn_KMStart and lcn_KMEnd, sorry if that aided any confusion, will do it change.

[LaRetta]

Hi Wim,

 

This is another attempt at this thread where we have strongly suggested restructure.

 

http://fmforums.com/forum/topic/94260-creating-sum-of-amount-field-for-a-found-set-summary/

 

 

Hi Miss A,

 

FMForums prefers that you stay on same thread instead of starting a new topic, otherwise Wim (and others who may attempt to help) will need to spend a lot of time again asking all the same questions and it only confuses when you post duplicate posts on same issue.  

 

I am pleased you resolved your issue but that script is frightening.  With the time you've devoted to finally (?) achieving your result, you could have simply make a few structure changes and been done with it.  We had offered to help you make those changes and I again strongly suggest it.

[Lee Smith]

Please do not multiple post your questions. I have merged your two threads, and modified it to show only one of the LONG SCRIPTS.