How to retrun the value from lookup, rather than the reference

I feel like this is probably something every simple indeed.

I have a range of tables holding various data for football events, where I invite players from a contacts list. I want to pull data from various tables into a formatted table that I then use an external application to automatically send an invite via xmpp. This is a “Players Invited” table whose source is “Players” and selected by “INVITE Card - Inviite” - where invite card has a reference list of players invited “Invite” by “Event” - type DateTime.

I need to reference the value of the current subscription cost balance of the player, based on a running total kept in a separate table. A record of this is kept in a table “Atttendance”, where an “Attendee” has a running “Balance”. I just want to copy this number to my “Players Invited” table.

I can’t see how to do it.

In the table “Players Invited” I have tried to reference the “Balance” using:


With a column type Reference, Data from “Attendance” and show column “Balance”

This appears to correctly reference the data…but it returns a cell reference in the form "Attendance[[x]] " rather than the actual data (number, type currency)

I’ve tried various things, mostly resulting in some form of error, nothing returning the number I want. This is obviously me missing something very simple indeed…but I can’t figure it out.

Any suggestions appreciated.

Congrats, this is almost perfect and it’s really unfortunate that it doesn’t work. Performing the reverse lookup, setting the column type, and setting the show column are all things that most users don’t get right.

The problem is probably that lookupRecords returns a ReferenceList, not a Reference, and it’s not happy about that mismatch. Maybe we can make that smarter. Try using lookupOne.

Great catch. That works perfectly in that case. Thank you very much.

I have a similar case, in the same table, that is more problematic. I think…

As part of the invitation list formed by “Players Invited”, I want to specify the event (game) that I am (currently) inviting players to. This field is therefore derived from "Invite” (rather than Attendance), and subject to a players name appearing in the “Invited” list in invite.

If I use:


It gets a list of all the events the player has been invited to (the next events, and previous events they were on the list for). In the form:

[AltText(‘2023-07-06 8:00pm’), AltText(‘2023-07-13 8:00pm’)]

If I use lookupOne I get a better formatted response.

2023-07-06 8:00pm

…but it’s the first event that player was invited to, not the Event currently selected on the invite card (or the next event). Ideally I would like a way, prefereably by selecting the evnt on the inviote card, of pulling the event details to which I am inviting the player…but I can’t select the widget to select “Players” by “Invite Card - Event”, not least because it’s not an option, but because “Players Invited” needs to be selected by “Invite Card - Invite”.

Some sort of date selection? Is there a way of selecting a reference list by two criteria? (pivot?).

I’m having trouble following. Please can you explain the structure of the document? Tell me each relevant widget, the source table of each widget, the relevant columns of each table, their names, types, and formulas, and the behaviour you want (e.g. “Widget C should show rows that have to the rows selected in widgets A and B”)?

My guess for why you’re seeing AltText is that the values in ‘Event’ don’t match the column type, e.g. they’re string/text values that haven’t been correctly parsed to the DateTime type of the Event column, and are showing in pink/red. Does that sound right?

“Select by” between different tables needs a reference or reference list column (or a summary table, which I’m guessing is not relevant here) so extracting the ‘Event’ column will not help as you will have a list of dates/strings instead of a list of references. Stick to Invite.lookupRecords(Invite=CONTAINS($id)) and set the type of the column containing that formula to ReferenceList, and then make it show the Event column in the column configuration rather than in the formula. Hopefully that will be a step in the right direction.

I’m not sure that I could adequately and quickly explain the structure of the tables/widgets. I’m going to have more of a play with this when I have more time. Probably at the weekend.

I appreciate the help and suggestions. I think the first step is me understanding better what I have, and what I’m trying to do.