Show a random record value in a Text Display?

[Jbmanos]

I have a database with around 10,000 rows and the material is mostly quotes. So I don’t want to edit them, but I’d like to pull a random entry. The field of interest contains HTML text, and I’ve already figured out that I can set two Text Displays on a form and set one of them to be Rich Text and it must use NS rendering to convert it (it misses HTML entities like &quote; but gets all the format tags right – same behavior I’ve seen in applescript. I can see this being very handy for a report – think random quote of interest on a letter, invoice, etc.

So, perhaps clumsily, I’ve managed to piece this code together that does fetch a random field - the Field is named Text (I known that I should probably rename that):

      local Entries, winner
      let Entries = arraybuild(cr(),"",{Text})
      let winner = randomarrayelement(Entries, cr())

I’ve also used it as a Text Display’s formula by putting it in an execute||| block and added: functionvalue winner

I prefer to have it on a button I press, so if I don’t like the first random one, I can click and get a new one before committing it to the use in the eventual form/report/etc.

Two problems I can’t figure out:

1. if it’s on a button, how to I put the value into the Text Display? I’ve named the Text Display frmWinner and tried all sorts of hacks but nothing successfully so far.
2. if I leave it in the Text Display, what sort of button code would force the Text Display to reload?

One the side, this made me come up with an alternate hack – how could I change the focus of the form to be the single random record from the button? So far, I’m not seeing a way to just go to a random record – I can pick a random, I just don’t get how to go to record X and have the form focus on it.

I think I will runto more trouble when I try to pass this along to another procedure later, but maybe not – since storing it in a variable should make it easy to pass the quote along.

[Jbmanos]

I found a kludge that works when doing this on a form – it doesn’t solve setting the value of a Text Display and refreshing, though. But if anyone is interested in moving to a random record, I think this works – the key was using noshow to make it respond instantly:

let tNumber = info("records")

let tWinner = randominteger(1,tNumber)

noshow
firstrecord
loop
   downrecord
   tWinner=tWinner-1
until tWinner=0
showline
endnoshow

If there’s a more elegant way to do this, I’m all ears! I’m just hacking my way through a project

[gary]

How about this one-liner:

findid randomarrayelement(arraybuild(cr(),"",{info("serverrecordid")}), cr())

[Jbmanos]

Nice – I did not find the findid when digging into the Panorama help – that’s the ticket!!! I’m at the stage of learning how Panorama describes things so, this helps me a lot!

[admin]

I think your first approach was on the right track, but with some adjustments could meet all your specified needs.

First of all, you don’t need to use execute - this simple formula will do the job.

randomarrayelement(arraybuild(cr(),"",{Text}),cr())

Now let’s modify this formula so that you can click and get a new entry. To do that I’ll add the ignore( function to the end:

randomarrayelement(arraybuild(cr(),"",{Text}),cr())+ignore("",updateText)

This function will always return empty text, so it doesn’t change the displayed text. But if a variable named updateText is modified, that will cause the Text Display object to recalculate the formula. So all we need is a button with this code:

showvariables updateText

Pressing this button tells Panorama that the value of updateText has changed, so any object that uses updateText in a formula will be displayed. (I’d be tempted to not use a separate button - if you attached this code to the Text Display object then you could just click that object to update the text.)

Notice that there isn’t actually any variable named updateText – it was never created! For this application it doesn’t matter, because the variable is never actually displayed, just used to trick Panorama into refreshing the display. (Also, the “fake” variable doesn’t have to be named updateText, it could be any name you want as long as you are consistent.)

This now does everything you ask, but I think it’s not really going to work the way you want. The text will update whenever this Text Display is displayed. So if you close and re-open the form, the quote will change. More importantly, if you print this in a report, you’ll get a different quote than the one you see on the screen. When the report is printed, the formula will be calculated, so it will show a random quote, not the one you picked.)

I think what you really want to do is store the quote somewhere, either in a fileglobal variable or in a field. Since I don’t really understand your application I can’t advise which. Well, if you want the same quote on every instance of this form, use a fileglobal variable. If you want each record to have a different quote, use a field.

[Jbmanos]

@admin

I think what you really want to do is store the quote somewhere, either in a fileglobal variable or in a field. Since I don’t really understand your application I can’t advise which. Well, if you want the same quote on every instance of this form, use a fileglobal variable. If you want each record to have a different quote, use a field.

This is TBD yet – ultimately, the master database is assembling bits and pieces from some ten or so databases – this random quote is just one – so once I like what I want, I will save the chosen quote in a field for the record for the day being built… So far, I’ve got six of the components working. I’m using a database and have the ten others as auxiliaries, and using a form in the master, compiling the components – seems to be what I wanted to do in applescript years ago but was too lazy to build the dialogs for the app! LOL Panorama has made a lot of this wayy easier to wrangle! So much so that I’m actually building the project now instead of just thinking about it and doing it by hand instead! hahahahah

To add more about the project – overall, it’s to make a calendar post for wordpress of bits of information relevant to a particular day in the future, and post the info to a wordpress site, schedeuled to post on the day, etc. So for any provisional date in the future, I’m pulling random date related, and some topically related bits. In this case, I’m looking at a form with a calendar picker – once I pick a date, it is looking for records in the database, if nothing there, then make a record and start populating pieces, etc. Once it’s poluated, push a button and send the stuff to wordpress. Picking a quote is one that still requires some human touch, however – let’s say a date is a memorial of a person – if the random quote clashes with an aspect of that memorial, I’d pick a different one, etc. Or, perhaps a day with a special event – maybe there is one quote that resonates more than another – once I find it, click a “save” button, push the value into the field for that day, and move to the next component. It’s that review that has prevented total automation – but copying.pasting, formatting, finding the picture, etc… all that stuff is ripe for this!

Things that are excellent for automation and such are the date related items: Some date information is easy to get because it pertains to a fixed date - say Christmas happens on Dec 25th every year, easy enough. Other information, such as when advent begins requires some procedure to determine – it’s the fourth sunday before Christmas, etc. Others are even a little more obscure – happens on second week after another event, but not if that day falls on a Sunday, and if it does, then move it according to rules. Tedious, but that’s what automation is for, right?

[Jbmanos]

@admin

Now let’s modify this formula so that you can click and get a new entry. To do that I’ll add the ignore( function to the end:

randomarrayelement(arraybuild(cr(),"",{Text}),cr())+ignore("",updateText)

I got it to work! I went in another direction – using a button to initiate the selection and plop the result in a variable. It won’t win any UX competitions, but works for what I need:

Your code worked perfectly for one window… by the time I was in the parent database and considering how I was selecting the quote, and how to store it, I came up with this – The button does this:

letwindowglobal winnerText = randomarrayelement(arraybuild(cr(),"auxiliary database",{Text}),cr())
showvariables winnerText

The left is a Text Display object just set to winnerText in the formula. The right is a web browser object that I erased the <head> and replaced the body text with «winnerText»
Works a charm! I’ve added some conditional tests to it also. the html part is ultimately what will get used, so it’s nice to review it but having the rendered HTML right there is awesome since it makes it obvious how it will look once put in place. I’ve been doing that with some of the other forms also and it’s making the the little task panel form I’m building way more fun and faster to make decisions.

I learned a lot along the way – and came up with some new questions. The big thing I finally figured out was why you called the variable a “fake” variable – I get it now. I tried using updateText and was having all sorts of problems… then I tried this:

randomarrayelement(arraybuild(cr(),"",{Text}),cr())+assign("",winnerText)+ignore("",updateText)
and
randomarrayelement(arraybuild(cr(),"",{Text}),cr())+ignore("",assign("",winnerText),updateText)

I couldn’t get either of those to work… so reading later, I see this note in the help at the very end of “Fields”: “Note: do not use local variables for forms” or something ominous like that? why not? So, I went and made assignglobal( variants… those didn’t work either. ? I’m still confused as to why these didn’t work – this is perhaps the worst part - it seemed like I should have been able to pass that into a real variable and use it elsewhere – and if the assignglobal( wasn’t the way that should have been possible, then I’m even more confused! LOL

So I’m finding variables elusive on forms as far as working with passing them out of Form formulas at least… At one point, I was trying to read the contents of a Text Display object from another Text Display, and I couldn’t find a way to do that either… So, that’s what got me to punt and use a button. Realistically, that’s probably the best solution for what I doing as this form is where I will build a new composite post and save it to a new record in the master. The button can also ensure that no random entry is already stored in the master database for a matching date – if it does, then load the current entry and force an “are you sure?” before loading a new random.

This whole thing, and being able to make a selection and automatically extract the selection is perfect for some data preparation tasks… you know every once in a while, you have a pile of text that you know writing regex to parse would be as painful as hand copying/pasting? Or, I’ll use DevonThink a lot on those files, and right-click, split document, etc… I had built a little applescript that presented a text pane and would extract selections - it was always clunky, easier to hand split. I digress, though. I’m just happy because there’s a lot of nice toolkit here -

[admin]

@Jbmanos

letwindowglobal winnerText =

Since I think you ultimately want to print this, you probably need to use letfileglobal instead of letwindowglobal. The windowglobal variable will be out of scope when the form is printed, so you’ll get an error. A fileglobal variable will be in scope any time the database is open. Fileglobal is usually the right choice 99% of the time for variables used with forms. The only time windowglobal would be used would be if different windows would use the same variable name for different values. Usually this would only be for “clone” windows, a rarely used (but very cool) feature.

What do I mean by “scope”? This has to do with the life cycle of a variable. Global variables have infinite scope, but other types of variables are only accessible under limited conditions. This is a really important topic.

Variables

@Jbmanos

“Note: do not use local variables for forms” or something ominous like that? why not?

Again, this is because of scope. A local variable only exists (is “in scope”) for the duration of the procedure that created the local variable. When the procedure is over, the local variable vanishes. This is no good for forms, because the form is trying to display a variable that has vanished. Panorama won’t crash or anything, but the form will display an error message, just as it would if the variable had never been created at all. As far as the form is concerned, the local variable never was created.

@Jbmanos

assign(“”,winnerText)

There are two problems with this. First of all, the second parameter, the field or variable name, must be quoted. The way you have written this, the assign( function thinks that winnerText contains the variable name, not that it is the variable name.

Secondly, you are assigning the value “” to the variable. That’s not what. you want.

I could rearrange this for you, but even with these problems fixed, I agree that this is not the correct approach. Your button is the right approach, except for changing from windowglobal to fileglobal.

@Jbmanos

why you called the variable a “fake” variable

I called it that because there was no variable. Just a placeholder name, which turns out to be enough for showvariables to work.

@Jbmanos

I was trying to read the contents of a Text Display object from another Text Display, and I couldn’t find a way to do that either

Yes, there’s really no way to do that.

Actually, that’s wrong - just a few months ago a new feature was added to make this possible. But I don’t think this is what you want to do in this case.

Text Display Programming

@Jbmanos

So, that’s what got me to punt and use a button. Realistically, that’s probably the best solution for what I doing

I agree. I don’t think this is “punting” at all - this is the optimum solution.

[Jbmanos]

Jim, thank you for taking the time to write these replies to me! It’s helping get to speed with Panorama very quickly and I like it!

Cheers, John