Creating a portal record script

[cheerfulzebra]

Hi...Please Help!

I am designing a database for out maintenance guys. I have a table called "Machines", this contains all the machines we have with serial details etc, I then have another table called "Service Info", these are linked with a primary key called MachineID. On my machines form layout is a portal that shows all the services for that particular machine. I want to create a script that adds a service file for that particular machine and copies over the last service date, how ever what I tried doesnt work because it take the last record within the whole of the service info table and uses that, in otherwords it copying another machines service date. This is the script.

 

Please Help!!!!

 

 

[rwoods]

Assuming the portal on your MACHINE layout is sorted with the most recent service at the top, then you could set some another variable (before the 'New Window' step) to capture the last service date by doing something like :-

Go To Field ("Any field in your portal")

Go To Portal Row (First)

Set Variable ($lastServiceDate , <lastServiceFieldname>)

This should be simple and work fine. You could also use an execute SQL statement to get the most recent service date, replace the three lines above with something like :-

Set Variable ($lastServiceDate ,

ExecuteSQL ("

SELECT lastServiceDate

FROM ServiceInfo

WHERE MACHINE_ID = ?

ORDER BY lastServiceDate DESC

FETCH FIRST 1 ROW ONLY" ; "" ; "" ; Machines::MACHINE_ID)

)

 

Hope that helps

Edited September 7, 2017 by rwoods

[cheerfulzebra]

Thanks ever so much, I will give this a go tomorrow and let you know the outcome! Execute SQL looks daunting but I might give it a go!!!

Thanks rwoods