Search to find linked value

[ncbaratta]

I'm trying to grab the value of the 'Full Student Name' from the 'Eligible Students' table by grabbing the 'id' found in the 'Monthly Registration' table and searching for it in the 'Eligible Students' table. The 'Eligible Student' is a linked field found in the 'Monthly Registration' table.

eltable = 'Eligible Students'
monthtable = 'Monthly Registration'
regtable = 'Registrations'
registrations = Airtable(key, regtable, api_key='key...Ep')
monthlies = Airtable(key, monthtable, api_key='key...Ep')
students = Airtable(key, eltable, api_key='key...Ep')

for month in monthlies.get_iter(view='Attended any'):
    for m in month:
        count = count + 1

        el_id = m['fields']['Eligible Student']
        flname = students.search('id',el_id,fields='Full Student Name')

        cur.execute("SELECT id FROM registrations WHERE first || ' ' || last = ?", (flname[0],))

        data = cur.fetchone()[0]

        cur.execute('''INSERT OR IGNORE INTO attendance (reg_id, workshop_id)
        VALUES ( ?, ? )''', (data, m['fields']['Monthly Workshop'][0]))
        conn.commit()

I'm not sure what I've done wrong, but here's the error I get when I try the above code:

Traceback (most recent call last):
  File "stats.py", line 295, in <module>
    flname = students.search('id',el_id,fields='Full Student Name')
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 338, in search
    records = self.get_all(**options)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 273, in get_all
    for records in self.get_iter(**options):
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 237, in get_iter
    data = self._get(self.url_table, offset=offset, **options)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 175, in _get
    return self._request("get", url, params=processed_params)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 171, in _request
    return self._process_response(response)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 161, in _process_response
    raise requests.exceptions.HTTPError(err_msg)
requests.exceptions.HTTPError: 422 Client Error: Unprocessable Entity for url: https://api.airtable.com/v0/app.../Eligible Students?fields[]=Full+Student+Name&filterByFormula={id}=[u'recyaAPrk3b74OnGz'] (Decoded URL) [Error: {u'message': u'The formula for filtering records is invalid: Invalid formula. Please check your formula text.', u'type': u'INVALID_FILTER_BY_FORMULA'}]

[kpbear]

could it be that you aren't defining... registrations = Airtable(.... at the top where you have the other Airtable defs. not sure if it is needed since you are using monthlies.

[ncbaratta]

Oops! I am, I just didn't remember to list it here.

[ncbaratta]

Updated my original post.

[bpeterso2000]

Based on the portion of the error message filterByFormula={id}=[u'recyaAPrk3b74OnGz'] it looks like el_id, i.e. the field_value is a list rather than a string. You may want to iterate through el_id and then call search for each record ID.

Airtable.search(field_name, field_value, ...)

        Args:
            field_name (``str``): Name of field to match (column name).
            field_value (``str``): Value of field to match.

For example if el_id was a string then students.search('id', 'recyaAPrk3b74OnGz', ...) would translate into a URL parameter like filterByFormula={id}='recyaAPrk3b74OnGz'.

[ncbaratta]

You were right @dpeterso2000 , but now I have new error :(

Traceback (most recent call last):
  File "stats.py", line 296, in <module>
    flname = students.search('id',el_id,fields='Full Student Name')
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 338, in search
    records = self.get_all(**options)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 273, in get_all
    for records in self.get_iter(**options):
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 237, in get_iter
    data = self._get(self.url_table, offset=offset, **options)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 175, in _get
    return self._request("get", url, params=processed_params)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 171, in _request
    return self._process_response(response)
  File "/Library/Python/2.7/site-packages/airtable/airtable.py", line 161, in _process_response
    raise requests.exceptions.HTTPError(err_msg)
requests.exceptions.HTTPError: 422 Client Error: Unprocessable Entity for url: https://api.airtable.com/v0/app.../Eligible Students?fields[]=Full+Student+Name&filterByFormula={id}=recyaAPrk3b74OnGz (Decoded URL) [Error: {u'message': u'The formula for filtering records is invalid: Unknown field names: id, recyaaprk3b74ongz', u'type': u'INVALID_FILTER_BY_FORMULA'}]

I wonder if you can't filter on the ID? Is that possible? I know it's not a value that's shown in airtable at all.

My code:

eltable = 'Eligible Students'
monthtable = 'Monthly Registration'
regtable = 'Registrations'
registrations = Airtable(key, regtable, api_key='key...Ep')
monthlies = Airtable(key, monthtable, api_key='key...Ep')
students = Airtable(key, eltable, api_key='key...Ep')

for month in monthlies.get_iter(view='Attended any'):
    for m in month:
        count = count + 1

        el_id = m['fields']['Eligible Student'][0]
        flname = students.search('id',el_id,fields='Full Student Name')

        cur.execute("SELECT id FROM registrations WHERE first || ' ' || last = ?", (flname[0],))

        data = cur.fetchone()[0]

        cur.execute('''INSERT OR IGNORE INTO attendance (reg_id, workshop_id)
        VALUES ( ?, ? )''', (data, m['fields']['Monthly Workshop'][0]))
        conn.commit()

[bpeterso2000]

I wonder if you can't filter on the ID? Is that possible?

Not when the column type has "Allow linking to multiple records" enabled. But looking at your code I don't think you need to filter on ID.

Are you trying to retrieve the Full Student Name from each record in the registration (monthlies) table? If so you could do something like this ...

for page in registrations.get_iter():
    for registration in page:
        for student_id in registration['fields'].get('Eligible Student', []):
            student = students.get(student_id)
            full_name = student['fields'].get('Full Student Name')

Rather than search for the record ID, you can use get(record_id).

[ncbaratta]

Thanks so much for trying to help out!! The problem is a little more complicated.

As I tried to explain it here I had to delete all that I wrote :) hehe

In short - I think I'm making this way too hard and am going to tackle it a different way!! I do love how responsive you've been here and love this airtable wrapper!!!