Table lookups... help thanks

I am using appgyver hobby “database” ie flat tables for my DATA.

One table is as follows: MEMBER table. *ID is the generated field.

ID* NAME COMMENTS
111 Fred xyz
222 Sue abc
333 Frank xxxxxxxx

Then somewhere in my app, I have a table called EVENTS as follows
ID* EVENTNAME COMMENTS
123 skating cold
234 swimming pool

And a table that says who is coming to an event, so the table is called PARTICIPANTS
ID* EVENTID MEMBERID Comments
888 123 111 x
999 123 333 y

If I was to display table PARTICIPANTS to a user, I could go get the record for event 123. Then I want to see who is coming so I get all the records in PARTICIPANT filtered on EVENTID=123.

When I display the records, I would like to show the person’s name from the MEMBER’S table not the MEMBERID.

NOTHING I TRY WORKS. “FIND” gives me an error every time on the first “,”

I need a function that does the following:

SELECT MEMBER.NAME from MEMBERS where MEMBER.ID=PARTICIPANTS.MEMBERID. <<<<< SQL

I cannot find a function that works that does this. I am not using arrays, I am using a table.

Hi! I think you have to combine a couple of Formulas to make this happen. My first thought is to use FIND_BY_KEY and LOOKUP. You’d use the former to find the correct Object from the List and latter to get the correct field’s value. The Formula would look something like this:

LOOKUP(FIND_BY_KEY(data.MEMBERS, "ID", data.PARTICIPANT.MEMBERID), "NAME")

The above Formula assumes that we’re looking for just one (the first found) member to show. As multiple members can come to an event you’d need to make some changes if you want to get a List of those members. In that case I’d suggest you to use SELECT_BY_KEY:

SELECT_BY_KEY(data.MEMBERS, "ID", data.PARTICIPANT.MEMBERID)

Then repeat the output List and bind the repeated component values to current.NAME however you want to.

1 Like

Thank you for the quick reply. Still stuck. Here is the example in more detail:

I have the table MessageMember. It has a key: id
It also has MessageID, MemberID.

so what I would eventually like to display is: a row for each id in MessageMember…
id, message.text (from message table) and member.name (from member table).

so on the form i have a repeating group for id, messageID and memberID and 2 other fields to hold .text and .name

I bind them to the repeating group as well so that there is pone of each per row, then I try the formula.

latest attempt:
LOOKUP(FIND_BY_KEY(data.Members, “id”, data.MessageMember.MemberID), “Name”)

  • object type not assignable to text type
  • should use a number to access a list item instead of text type

id field is a key field (created by default) which is text (display only I cannot make this anything else in DATA definition section).

Thanks.

where can i find documentation pertaining to scope of variables/data. Eg. I am about to Create Record. It is created and a key (id) defaulted. How can I get that key before, during or after the create?

I think one of my problems is I do not understand what data variable are avail to me when. So trying to save values to page variables doesn’t work for me.

Hi, if you haven’t yet checked our tutorials, I think the Movie review app video shows quite well the basics of how to handle Data variables and might be helpful to you.

To your questions:

  • You can read more about Data variables here
  • Create record outputs the newly created record’s properties which include the ID (which if you’re using AppGyver Cloud Storage is created automatically and you cannot define it yourself). So find the id from the output either through direct binding or Formula (outputs...).

If/when you have checked the tutorials and still have issues with showing the data, I’d like to know a little more about your app, because to me the Formula you’ve now used looks correct, but I’m not quite sure what you’re repeating. Could you take a couple of screenshots and show the configuration to me?

I’d like to know what is the value that you’ve bound to Repeat with field and which type of component you have selected to repeat.

Got it! Thank you. But that said, using LOOKUP(FIND sometimes gives me weird circumstances. Here is an example:

Here is how this runs…

Alert: LoggedInMemberID was empty
Alert: “”

I then navigate to a different screen and come back…
Alert: LoggedInMemberID was empty
Alert: 5f32a…

WHY?

Now I take the unused Dialog Alert and drag it straight down so that it is inbetween the IF and the VARIABLES. I shut down IOS appgyver, and start again…

Alert: “”
Alert: LoggedInMemberID was empty
Alert: 5f32a…

Really, WHY? By placing an extra Alert and doing the FIND in it, makes the VARIABLES work?
What am I missing?

Thanks

Without the Alert with FIND, it will not work. I tried doing the FIND as a dummy command into a page variable, would not work. The following works, calling the form a second time.

Hi, this could be because the Data variable (data.Members) isn’t initialised before your logic arrives to Set app variable flow function. And when you add the alert before that, you give enough time for the Data variable to initialise.

So, my suggestion is to modify the logic so that it checks that the Data variable value actually exists when you try to use it. You could do this with an If condition checking !IS_EMPTY(data.Members) and then if isn’t empty continue with your current logic, and if it’s empty, then go to a Delay flow function and circle back to the new If condition after the delay (something like 10-25ms delay would work). This would make your logic wait until you have required data available.

Beautiful, that did the trick! Thanks. Would something similar be wrong with the following:

I try and update MEMBER field but after UPDATE it has not changed. If I change the flow and use the page Who is going EDIT Member, it works. Thanks.

Great to hear that the first issue was fixed :+1:

The new one is bit different. I assume you Update record comes out of the first output which means it actually was successful. But that means the change has been made to the backend, but not yet to the frontend, i.e. the Data variable (EventParticipants) you are updating doesn’t update in the app automatically when backend data changes. This means you should update it with Set data variable after the successful Update record. The Update record should have the newly updated record information in the output response, so you can use that to update the Data variable

Hi, unfortunately the update record does not update the record. if i leave and come back, the record has not been change.

Also, if i instead go to the EDIT page, when I return, I do not know what command to put where to show the records updated. ie i have to go back and page and then come back. THANKS.

Could you share me your app ID so I could take a look at this?

My app id is 101025 THANK YOU!

I cannot really test the situation now, I’d need a test account for that and you to change the logic back to the “broken” version as I don’t want to make changes to your app.

But, it seems that my previous guidelines was correct. You haven’t used Set data variable after you use Update record. That should be done in order to show the change on the page.

Also, as you navigate to another page and press Back, the page won’t mount but just focus. And as the only place where you Set data variable is in data variable’s logic canvas connected to Page mounted event, the data won’t update after the page focuses back.

So, make sure that the Data variable is up to date after you have pushed the changes to backend. Regarding this, when you Create record you open the same page again. This is bit odd. The only thing you need to do is use Set data variable in correct places, i.e. after you have made changes to backend data.

I tried to set data variable, for example here after the Create. But the page does not change. And it doesn’t seem to make sense to me, the only binding option is data variables, and it doesn’t allow me to specify the criteria or sort info as it does under DATA section of page.

As for the UPDATE flow… the update doesn’t even work and that baffles me. It does not update, ghe update does not fail, it just doesn’t update.

Okay, now you just Set data variable to its old existing value. It basically does nothing. You need to use the output of Create or Update record flow functions to really add the new record information to the Data variable.

For this you’d need to use Formulas to add or replace the created/updated record to the Data variable. You cannot directly bind it there since your Data variable is a List of Objects and created/updated record is a single Object.

In Create record case the Formula would look something like:

WITH_ITEM(data.EventParticipants, outputs["Create record"].response)

And with Update one option would look something like:

WITH_ITEM(WITHOUT_ITEM(data.EventParticipants, current), outputs["Create record"].response)

Another option would be to use Get record collection Flow function every time to fetch all records after each Create/Update or any change to data, and then Set data variable (EventParticipants) to be the output of the Get record collection.

Aha moment. Now I get it. Thank you for your expertise and patience.

So now everything works perfectly except my UPDATE. This is my only page where I tried to do the update per row using an UPDATE button per row. I must not be getting the values to the update properly.

Screen Shot 2020-09-11 at 10.08.47 AM

In this example I only want to change ParticipantID. I had it as current.ParticipantID, didn’t work.

You can try to use debugger or e.g. Alerts to see what information is sent to the backend and what comes back. On web version you could also check console network tab for details. Maybe that would point you to right direction with the missing data.