Hi guys,
This time I want to do a JOIN between tables to filter data.
This is the e.g case:
So I want (ALL) the data from table 1, but filtered by the STATUS field from table 2.
Is this possible on appgyver?, I don’t know if a double SELECT would do the trick, hope somebody can help me.
Thanks in advance.
You could do it by using FIND_BY_KEY inside SELECT to find the status of the item in table2 and compare it to your wanted status:
SELECT(appVars.table1, FIND_BY_KEY(appVars.table2, "id", item.id).status === "wantedStatus")
Hi Mari,
Thanks for the information, I will check if works, I was trying with IS_IN_ARRAY_BY_KEY but no success.
UPDATE:
I tried but I think there will be a problem because my “ID” is not a field, is the document name, so I need to split it to find the correct ID, because if I try directly, it won’t match, because the name has the whole path and the tables has differentes path obviously.
This is an example of what Im trying to explain:
I can do an SPLIT on the “name” inside the FIND_BY_KEY parameter? to match the id’s?
Yeah, you could incorporate a SPLIT(item.id, "/")[5] formula to just compare the names
Hi once again Mari,
I tried what you suggest to do with the split, but its still doesn’t work, I’m using this in a data repeat:
SELECT(data.payments_pending1, FIND_BY_KEY(data.new_shipments1, SPLIT("name", "/")[5], SPLIT(item.name, "/")[5]).fields.payment_status.stringValue === "Pendiente de pago")
But it doesn’t show any data 
what can I missing on this formula?
Hi @Pedro_Tovar, it’s a bit tricky when you have to do the splitting on both lists.
Maybe the simplest way would be to start off with a MAP + SET_KEY – you transform both lists, so that each item’s id property is set to the value of SPLIT(item.id, "/")[5]:
MAP<payment>(data.payments_pending1, SET_KEY(payment, "id", SPLIT(item.id, "/")[5])))
MAP<shipment>(data.new_shipments1, SET_KEY(shipment, "id", SPLIT(item.id, "/")[5])))
Now that the ids have been reformatted, you can use the lists in the original formula:
SELECT(<payment list>, FIND_BY_KEY(<shipment list>, "id", item.id).status === "Pendiente de pago")
So finally it becomes
SELECT(MAP<payment>(data.payments_pending1, SET_KEY(payment, "id", SPLIT(item.id, "/")[5])), FIND_BY_KEY(MAP<shipment>(data.new_shipments1, SET_KEY(shipment, "id", SPLIT(item.id, "/")[5])), "id", item.id).status === "Pendiente de pago")
which is a monster of a formula, but it should work! Let me know if there are any issues with the aliases etc.
Understand the idea, but got the next errors (I just changed the “id” string by “name”), because my field name on the backend is “name”.
Something is happening with the “item” reference.
If you look at the list of errors, first it looks like I have missed a closing brace in MAP formula, for each there should be three closing braces at the end:
MAP<payment>(data.payments_pending1, SET_KEY(payment, "id", SPLIT(item.id, "/")[5])))
That will probably fix quite a few of the errors already.
Then the “object and text type” error comes from just using status instead of fields.payment_status.stringValue
If the “item” references inside SPLIT aren’t recognized, they should probably be switched to payment and shipment respectively, would that fix the error?
Hi Mari,
Sorry for the delay in my response, had some difficults days.
Now I’m trying to solve this, I tried many times and finally found the way to no errors on the formula, but my repeated list still empty. 
On my database, I have the same document with the same ID in both tables, and in the new_shipments table there is a field called payment_status and his value is “Pendiente de pago”.
What do you think I still doing wrong?
Here are some images about the formula.
Thanks in advance
NVM, I didn’t have my data vars with auth to DB 
I will let the formula right here just for those who need to learn how I did it with the help of @Mari
SELECT(MAP<payment>(data.payments_pending1, SET_KEY(payment, "name", LAST_ITEM(SPLIT(payment.name, "/")))), FIND_BY_KEY(MAP<shipment>(data.new_shipments1, SET_KEY(shipment, "name", LAST_ITEM(SPLIT(shipment.name, "/")))), "name", item.name).fields.payment_status.stringValue === "Pendiente de pago")
1 - MAP It just rename the whole set of data called from data.payments.pending1
2 - After that, the SET_KEY call the payment data set, and search for the field “name”, which I did a Split on it and select the last item on the list from that split.
3 - Now, with the FIND_BY_KEY you do the “join” to the other table and in the last part I filter by and specific field inside that last table.
If you have any doubt, feel free to ask and I will help for sure.
Thank for all your time and help @Mari