Searching through records to return latest date.

[Miss A!]

Hi guys, me again - everyday 

 

i am stuck with a script this side - i am trying to get around updating a layout field with date values, i am searching through records where by multiple records are belonging to the same entity except that they are entered on different dates. I have created my script working fine upto the point where it returns the records i want to search through. Now from the existing found set, i would like to loop(any other solution to go by) through, find group of records that are related by ID and return the record with the latest date.

 

I have attached script am messing about with........Just what i want is solution to return the records matching each particular __ID_Vehicle_Transport ID field for the latest date.

 

 

Thank You.....

[eos]

Not quite sure if you want to do something with the latest related record for one parent record, or a bunch of them. It would be helpful if you indicated the actual purpose, and to see your existing script because …

 

I have attached script am messing about with

 

… you haven't done that. 

 

multiple records are belonging to the same entity except that they are entered on different dates. I have created my script working fine upto the point where it returns the records i want to search through. Now from the existing found set

 

That would simply require Go to Related Record[] …

[Miss A!]

Sorry, i did not make use of the next step after selecting file to attach - i have done that now.....In the mean time i should try the function  Go to Related Record......

ServicePlan.ps

[Miss A!]

Not quite sure if you want to do something with the latest related record for one parent record, or a bunch of them. It would be helpful if you indicated the actual purpose

 

Yes, i would like to put that records date field onto another layouts date field of the record it relates to........

[eos]

Yes, i would like to put that records date field onto another layouts date field of the record it relates to........

 

I'm afraid that doesn't really clear things up. Can you describe without database terminology what you're doing, or trying to?

 

btw, since you're on a Mac, you can

• open the script

• press Cmd-P

• in the Print dialog, select “Open in Preview”

• copy the script code from the newly opened PDF window in Preview

• paste it here in your post

 

 

Looking at your script now:

 

Don't use Insert Calculated Result; use Set Field[] instead. And instead of using Constrain, you could probably add the conditions to the initial Find conditions (but then I don't understand what you're after well enough; maybe it does make sense …)

 

Your loop has an essential flaw; a check that involves a variable that was set only once outside the loop, and Get ( Current Date) – namely,

 

If [ $ADate ≥ Get(CurrentDate) - 200 ]

 

– will have the same result for every record. You need instead to check against a record value. What you're intending is probably

Set Variable [ $ADate ; Get(CurrentDate) - 200 ]
…
If [ Table::aDateField > $ADate ]

 

But then, of course, instead of using a loop with a check, you could add the date to your search criteria

Enter Find Mode
Set Field [ … ; … ]
Set Field [ Table::aDateField ; ">" & Get ( CurrentDate ) - 200 ]
…
Set Error Capture [on ]
Perform Find
If [ not Get ( FoundCount ) ]
# error handling code

[Miss A!]

Ohh, i got even more confused if not just lost, i think your explanation is too deep for my not more than a month old filemaker self learning - ok, let me explain again......

 

Just until the sort records, i think i know and understand what i am getting at that point - that is a foundset/records whose vehicle status is A and KM Reading field is not empty, then i sort according to Vehicle RegNr and the Date_of_Action(Date record was entered) this gives me the latest date at the bottom of each group of records per RegNr - From there with those records, i am suppose to pickselect the latest date of action and set it to a field on another layout for a particular vehicle - Vehicles are unique by Vehicle RegNr field. So for Vehicle RegNr N6671W i may have three records with different dates 10.11.2011, 11.12.2012 and 15.08.2013, set field Date for the RegNr record N6671W to the date 15.08.2013 So i am stuck at how and when to move to the next record for a particular vehicle.

[eos]

The problem is that you don't just tell in plain English what you're trying to do – e.g.

 

“I have a table with vehicles, and a related table with inspection events (?). I want to set a field in Vehicles for all Vehicles with status so-and-so to the latest date of an inspection event that includes a km reading"

 

… at least this is how I interpret your description.

 

See the attached file for several ways to do this; be sure to read the comments in the script.

 

Note that the relationship works via a meaningless serialized ID, not the registration number.

 

And why do you need to store the latest event date in Vehicles? If this is only for display, then just create a filtered portal with the condition 

not IsEmpty ( TheOtherTable::KMReading ) and Vehicles::Status = "A"

set it to display one row only, and sort it by TheOtherTable::theDate, descending.

 

If you want to have the value in the Vehicles table, but it can be unstored, then you could try a calc field (unstored, result type date!) where

Case (
  status = "A" ;
  Let (
    sqlDate = ExecuteSQL ( "
    SELECT Max ( theDate ) 
    FROM Actions 
    WHERE 
      id_vehicle = ? AND 
      KMreading IS NOT NULL
    " ; "" ; "" ; id
    ) ;
  Date ( Middle ( sqlDate ; 6 ; 2 ) ; Right ( sqlDate ; 2 ) ; Left ( sqlDate ; 4 ) )
  )
)

 

UseDateFromRelated_severalMethods_eos.fmp12.zip

[Miss A!]

Thank you for your good efforts Eos, i will explain again and while you try to understand me, i will be trying your idea of solution but without portals (if that will be possible then) because i am not yet well comfortable with them portals - ok, here we go again,

 

I have two tables, Remittance and Service, Remittance has always existed, and Service is a new table that i would like to use to create a service plan layout.I have in Service three fields as edit boxes for RegNr, ServiceInterval and VehicleName, also two more fields in Service table that is Date and KM_Reading(Reading as on that date) are using data that exist in Remittance, so i have used the Date field from Service to form relationship with Date_ofAction field in Remittance so when i place(Manually) a date from Remitance onto the Date field in Service, then the KM_Reading also displays(This operation i would like to do with use of a script) - Now in Remmittance table the field Vehicle_ID is matched to RegNr in Service using the middle words(Remmitance::Vehicle_ID;2;1)so that the Date(Latest) field in Service is updated when there is a match between RegNr and the value filtered from Vehicle_ID (For example a vehicle_ID of "15 - N55544W - Microbus 2,6" will match RegNr "N55544W" in the service table), so for a number of records with the same Vehicle_ID, the Date, the record with the latest date should be used onto the Date field for the specific vehicles..........

[Miss A!]

Hi - this is what i ended up with and it seems to be working, thought i should share, any one everyone feel free to evaluate and comment.......Thank you!

 

Service Plan: Service Plan
Go to Layout [ “Service Plan” (Service Plan) ]
Perform Script [ “Clear SP” ]
Set Error Capture [ On ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ] Enter Find Mode [ ]
Set Field [ Remittance_Transport::Ledger Accounts; "1010 Service" ] Set Field [ Remittance_Transport::flt__VehicleStatus; "A" ]
Set Field [ Remittance_Transport::KM_Reading; "*" ]
Set Field [ Remittance_Transport::Reference:; "==###*" ]
Perform Find [ ]
// If[Get(LastError)]
// Go to Layout [ original layout ]
// Show Custom Dialog [ Title: "No records found"; Default Button: “OK”, Commit: “Yes” ] // End If
Sort Records [ Keep records in sorted order; Specified Sort Order: Remittance_Transport::__ID_Vehicle_Transport; ascending Remittance_Transport::Date_Of_Action; descending ]
[ Restore; No dialog ]
Go to Record/Request/Page
[ First ] Loop
#Set Vehicle plate number to match Vehcile_RegNr in Service Plan
Set Variable [ $VHR; Value:MiddleWords(Remittance_Transport::Vehicle;2;1) ]
Set Variable [ $Veh_Count; Value:GetSummary ( Remittance_Transport::lxt_Vehicle_Summary ; Remittance_Transport::__ID_Vehicle_Transport ) ]
#
Set Variable [ $NextStart; Value:Get ( RecordNumber ) + $Veh_Count ]
Set Variable [ $ADate; Value:Remittance_Transport::Date_Of_Action ]
Set Variable [ $KMReading; Value:Remittance_Transport::KM_Reading ]
Go to Layout [ “Service Plan” (Service Plan) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: Service Plan::lxt_VehicleRegNr: “$VHR” ] [ Restore ]
Set Field [ Service Plan::lxd_Last_ServiceDate; $ADAte ]
Set Field [ Service Plan::lxn_KMReading; $KMReading ]
Go to Layout [ “Remittance_Ordinary” (Remittance_Transport) ]
Go to Record/Request/Page [ $NextStart ] [ No dialog ]
Exit Loop If [ $NextStart > Get ( FoundCount ) ] End Loop
Go to Layout [ “Service Plan” (Service Plan) ] 21 October 2014 11:43:10
Show all records