How to get all records from a module - SugarCRM version 7+

Question

According to the documentation, to query a record, you have to use this endpoint /<module>/:recordId, but what if I want to get all records from a given module ? I can't find any documentation for this.

I tried /<module>/filter to set json parameters like this :

{
    "max-num": "-1",
    "offset": "0",
    "order-by": "date-entered",
    "favorites": false,
    "my-items": false
}

But returns only 20 records ?

Of course I tried to customize "max-num" with plenty of numbers but still returns 20 records.

I need help

Answer 1

The option's name is max_num (containing underscore character), not max-num.

However there is another limit on the server-side that will overrule your max_num if its value is too high. The default server-side max limit is 1000, but that can be increased in config by setting $sugar_config['max_record_fetch_size'] to the desired number.

If you want to make sure that you get all records, it's generally a good idea to check next_offset in the response. As long as it is not -1 there are more records than were returned. In such a case you should send another request with the next_offset's value as offset in the request, to get the next "page" of records.

Notes

• I recommend to specify a fields array with the names of the fields you need in your requests. Otherwise you might end up with wasting a lot of time/bandwith/memory when retrieving a lot of records.
• Setting $sugar_config['max_record_fetch_size'] will also effect e.g. how many records you can with "select all" in the list views.

Paging Example

In the following example we are going to fetch all records from the server, in batches/pages of 1000 per request. For this example let's assume that the server has 2341 records in total.

We request 1000 records, starting at offset 0, which is the first record available:

Request #1

{
    offset: 0,
    max_num: 1000
}

Note: if no offset is specified, then 0 is assumed.

Response #1 from Server

                             {
                                 next_offset: 1000,
                                 records: [array of 1000 records]
                             }

We receive the first 1000 records (offsets 0 to 999) in Response #1, the offset for the first record that we do not have yet is 1000 as specified in next_offset

Therefore we request 1000 records again, starting with record at offset 1000:

Request #2

{
    offset: 1000,
    max_num: 1000
}

Response #2 from Server

                             {
                                 next_offset: 2000,
                                 records: [array of 1000 records]
                             }

We receive the next 1000 records (offsets 1000 to 1999) in Response #2, the offset for the first record that we do not have is 2000 as specified in next_offset

Therefore we request 1000 records again, starting with record at offset 2000:

Request #3

{
    offset: 2000,
    max_num: 1000
}

Response #3 from Server

                             {
                                 next_offset: -1,
                                 records: [array of 341 records]
                             }

In Response #3 we receive 341 records (offsets 2000 to 2340) and a next_offset with -1, indicating that there are no more records. That means we have now received all 1000 + 1000 + 341 = 2341 records available.

Overview of results:

 ______________________________
|                              |  \                  \
|                              |   |                  \
|         "Page 1"             |   |__ 1000 records    |
| records at offset     0..999 |   |                   |
|                              |   |                   |
|______________________________|  /                    |
|                              |  \                    |
|                              |   |                   |__ all 2341 records
|         "Page 2"             |   |__ 1000 records    |
| records at offset 1000..1999 |   |                   |
|                              |   |                   |
|______________________________|  /                    |
|                              |  \                    |
|         "Page 3"             |   |__  341 records    |
| records at offset 2000..2340 |   |                  /
|______________________________|  /                  /