Scripting an automatic find/sort/delete routine

[revjj]

Hypothetical file :

The file has one table with three fields.

Field one is a date.  Field two is data. Field three is data.

I would like to enter date and data.

If any date in a new record is the same date already entered then you would do a search for the record that has the original date entered.

Now you want delete the new record with the duplicate date.

With only one record now showing, you can now edit that record and save.

I want this to be invisible to the user.  Saving stuff, I'll work on later

Thanks

 

[Søren Dyhr]

You could define a selfjoin relation (with allow creation of related record sat) starting from a global field receiving the date in question, and the two other fields are in layout mode, are pasted in from a relation away, a portal is not required, since each entered date holds only one set of data from the two other fields.

The picking of dates could be done with a Drop-Down calendar filling the global field with the desired date, and if the date/record already holds, but the devil is in the detail and if you mimic this:

https://community.claris.com/en/s/question/0D50H00006ezbIfSAI/scriptable-popup-calendar-date-picker

...could the conditional formatting of each cell in the template be extended with a "ExecuteSQL (" to turn an occupied date ...say red, so the via the interface is warned ahead of the date selection.

 

--sd

 

[Søren Dyhr]

I've taken my liberty to hack Malcolm Fitzgerald's template ever so slightly, by incorporating the suggestion I made above to Revjj ... It must be taken with a grain of salt, there are waste and litter from Malcolms use of it, that makes no sense to keep.

But the hack requires at least fm16, due to the use of Cardwindows ... they're convenient to use because all the logic in the original interface can be inherited, hardly changed at all compared to a design using popovers.

I use the custom function here: https://www.briandunning.com/cf/1692 to turn occupied dates red, simply because it just requires a connection from the tables used here would a Cartesian suffice. Finally do I make use of a typecast with XOR....

--sd

 

Calendar.fmp12.zip