Need simple trigger script - Last Date in one Library from New Entry another

104 views
Skip to first unread message

james...@grainworks.beer

unread,
Mar 27, 2019, 8:47:35 AM3/27/19
to mementodatabase
I don't know Javascript, and I've tried to figure this out based on the example triggers that are available, but this has me stumped.  Any help would be appreciated.

I have two Libraries:  Accounts and Sales.  I would like the date field "Last Sale" in the Accounts library to be updated with the date from the "Sale Date" date field in the Sales library, every time a new entry is added to the Sales library.

The Accounts and Sales libraries are linked, so you can go into a particular account entry in Accounts and then using the Link to Entry function, you can add a new Sale in the Sales library that is linked to that particular account only.  So the trigger would be updating the "Last Sale" field in just that particular account entry in the Accounts library.

I'm also not sure where the trigger would reside.

Please let me know if you have any other questions.

Brother James

unread,
Apr 3, 2019, 8:10:53 AM4/3/19
to mementodatabase
Anyone? Even just a little push in the right direction would be appreciated. Or someone to say this is/is not possible.

Bill Crews

unread,
Apr 3, 2019, 10:10:27 AM4/3/19
to Brother James, mementodatabase
For a given entry in Accounts, you want the last date that any sale occurred for that account? Or the last date anything was ever sold? Or what? I'll assume the former.

In the Edit Library screen of the Sales library, select the Aggregation tab at the top. Choose the Max function and your date of sale field, then how you want to see it (suggest "Last sale:" for Prefix of result and either Left or Right and don't check the checkbox, but however you prefer), and indicate a Date result instead of the default Real result.

Now, in the list of entries of Sales, swipe the right side menu open (if it isn't already open on your device). Choose Group from that menu. Then select the field containing the account identifier (name, number, or whatever).

Now, you'll see Sales listed by account, with a bar representing each account. On each bar, you'll see the date of the last sale for that account. If you'd like this list in a different order, choose the Sort option, the field(s) you want to sort by (probably either date of sale or account identifier), and for each of them, whether to sort ascending or descending.

Brother James

unread,
Apr 3, 2019, 10:26:12 AM4/3/19
to mementodatabase
Bill, thanks for your reply!  However, what you've done is something I already have, but not what I'm looking for.  (It is useful though, to a degree.)

Let me attempt to clarify...
Two libraries:  Accounts and Sales.
Sales entries are already linked in Accounts, so you can initiate new Sales entries while in the Accounts library, and you can even see all the sales for a particular account in the Tracking page of the Accounts library.  I already have all of that set up.

What I am trying to do is take the date of the last new entry in Sales for a given Account and have that date show up in a SEPARATE field on a different page of the Accounts library (the Main page).  So without leaving the Accounts library, and not going to the Sales Tracking page (which shows ALL the linked entries), I can, at quick glance, on the Main page of the library, see the most recent sale date.

This has the benefit of convenience (shows up at a glance on the first screen amongst other most important info for that acct), as well as giving me a field that I can use to sort with or base reports on, when I export my data from the Accounts library to a spreadsheet, to do various reports for the office.

So, in summary: when a new linked Sales library entry is created, I'd like it to update the date in the "Last Sale" field in the Accounts library.

Bill Crews

unread,
Apr 3, 2019, 2:16:50 PM4/3/19
to Brother James, mementodatabase
In the Accounts library Edit Library screen, create a new JavaScript field Last Sale. I'll assume the Link to Entry field is called Sales and the field in Sales with the date of sale in it is called Date. BIG ASSUMPTION: The entries are entered in real-time sequence; this means the date of the last sale entered is the date of the last sale; if this is unacceptable, then let me know, and I'll send you a much longer script that also takes longer to run that removes the assumption. Either way, please let me know if this gets you what you want; in particular, the format of the date will probably not be what you like, so let me know what format you would like to see. Enter this in the script editor...

field("Sales")[0].field("Date");
Reply all
Reply to author
Forward
0 new messages