On data change, Populate another field

Just a quick example that someone might find useful. We have a UDF which determines whether a product is available for general sale. This is called “PBTI Status” after using the field for several years, someone asked for a list of all the products we have launched in the last 6 months. As we can go through an intense R&D process prior to go-live, we could not use the record create date and since we do not always sell an item on day 1 of launch, we could not use the first customer order date (although this would of been roughly accurate), and finally we do not audit this field and even if we did, we want something more accessible for the users so as they can quickly look it up.

Fortunately someone did have a list of launch dates, so I have created another UDF which they will populate, but going forward, as items are made “Live” we want to set the field automatically. This is just a quick demonstration of how to do it :-

The 2 UDF’s added are “PBTI Status” and “Launch Date” Sytelin_Items_Controls On “PBTI Status” combo box I have added an event to the “On Change” event as shown here Syteline_Component_Actions   The Event handler calls a form script as shown here :- Syteline_Event_Handler The form script is as follows :-

Sub pbti_setlaunchdate()

If ThisForm.CurrentIDOCollection.CurrentItem(“itmUf_LaunchDate”).Value = “”And“PBTI Status : “ & ThisForm.Components(“PBTI_DROP_PBTIStatus”).Value = “Live”Then

Application.ShowMessage(“PBTI Status : “ & ThisForm.Components(“uf_pbti_status”).Value)

ThisForm.CurrentIDOCollection.CurrentItem(“itmUf_LaunchDate”).SetValuePlusModifyRefresh(Format(Now, “yyyy-MM-dd”))

ElseIf ThisForm.CurrentIDOCollection.CurrentItem(“itmUf_LaunchDate”).Value <> “”And ThisForm.Components(“PBTI_DROP_PBTIStatus”).Value = “Live”Then

Application.ShowMessage(“You have just set the status to “ & ThisForm.Components(“PBTI_DROP_PBTIStatus”).Value & ” but there is already data in the launch date field.” _

& vbCrLf _

& vbCrLf _

& “Consider changing this manually.”)



Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.