# Generate sequential ID based on pulled data **URL:** \ **Category:** Support\ **Tags:** entities, form-design, odk-xlsform\ **Created:** [September 10, 2024, 9:47am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408 "2024-09-10T09:47:52Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Ayoub\_Kakande](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ayoub_kakande/32/27358_2.png) [@Ayoub\_Kakande](https://forum.getodk.org/u/Ayoub_Kakande)\ **Post date:** [September 10, 2024, 9:47am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/1 "2024-09-10T09:47:52Z")
**1. What is the issue? Please be detailed.** We do annual census in which we collect data from individuals that were in in the previous census, here we pull data from the previous census and validate the participant id (PTID), if an individual is new in the household e.g immigrant or new born we add them to the current census and create for them a new PTID. For example, if a household was censused in village 6 and household 205 and the last created PTID for another individual in that household was 6205045, the new PTID number in this household will be 6205046 for this new individual in this census. ODK must search from the pulled data (csv) check the previous number assigned in that village(6) and that household (205) and assign the next sequential number Any help will be much appreciated **2. What steps can we take to reproduce this issue?** **3. What have you tried to fix the issue?** **4. Upload any forms or screenshots you can share publicly below.** ---
**Author:** ![joybindroo](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/joybindroo/32/10263_2.png) [@joybindroo](https://forum.getodk.org/u/joybindroo)\ **Post date:** [September 23, 2024, 10:28pm UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/2 "2024-09-23T22:28:46Z")
Prepare another external dataset in which you only store maximum number value of the PTID for each household. When you want to add new member in household you use this value, increment it by 1, and then use it to assign as PTID to new individual member of the HH. You also need to keep updating this external dataset based on new daily updates in the main submission dataset. ---
**Author:** ![Ayoub\_Kakande](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ayoub_kakande/32/27358_2.png) [@Ayoub\_Kakande](https://forum.getodk.org/u/Ayoub_Kakande)\ **Post date:** [September 25, 2024, 6:43am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/3 "2024-09-25T06:43:33Z")
Thank you @joybindroo , could you be having a sample xls ---
**Author:** ![joybindroo](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/joybindroo/32/10263_2.png) [@joybindroo](https://forum.getodk.org/u/joybindroo)\ **Post date:** [September 25, 2024, 7:54am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/4 "2024-09-25T07:54:43Z")
# You can take similar steps for achieving the functionality - **Prepare the External Dataset** : Create a CSV file containing only the household number and the highest PTID for each household. This will allow the form to fetch the maximum PTID for the household and assign the next sequential number for new members. * * * - **Update the External Dataset** : Regularly update this dataset with new PTIDs after daily census operations or at the end of each census batch. This ensures that newly added members get the correct next PTID. * * * - **Modify Your XLSForm** : Use the `pulldata()` function to fetch the maximum PTID for each household from the external CSV and create logic to assign the next PTID to new members. * * * - **Handle New Households** : In case the household has no previous PTID (e.g., for a new household or missing data), the `pulldata()` function will return an empty value. You should set a default starting value for such cases. This can be based on the household ID, like starting from ` + 001`. * * * - **Deploy and Test** : After designing the form, test it using mock data to ensure that new members receive the correct PTIDs. * * * **_It is also crucial to ensure that the external dataset is being updated accurately for future rounds of the census._** * * * ---
**Author:** ![Ayoub\_Kakande](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ayoub_kakande/32/27358_2.png) [@Ayoub\_Kakande](https://forum.getodk.org/u/Ayoub_Kakande)\ **Post date:** [September 25, 2024, 8:07am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/5 "2024-09-25T08:07:54Z")
Thank you, this is really helpful ---
**Author:** ![LN](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ln/32/1605_2.png) [@LN](https://forum.getodk.org/u/LN)\ **Post date:** [September 25, 2024, 9:14am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/6 "2024-09-25T09:14:35Z")
> [@joybindroo](#): > > It is also crucial to ensure that the external dataset is being updated accurately for future rounds of the census. Instead of using manual updates, consider using [Entities](https://docs.getodk.org/entities-intro/) which let you share data between forms. You can think of an Entity List as a CSV attachment that's shared between forms and can be updated by form submissions. To generate sequential ids, you could use a household Entity List that keeps track of the highest participant ID assigned within that household as @joybindroo suggests. Submissions can then automatically update that highest ID. You can use `pulldata` in the same way as described. Another option would be to use a participant Entity List that keeps track of each participant, the household that they're in, and their number in the household. You can then use the `max` function to get the highest participant id used so far and use it as the basis for the next participant id. This would be a good approach if you also want to use participant information such as their name in future follow-up forms. Let us know if it would be helpful to see a worked example for one or both options. Currently, Entity updates require getting a form update from the server but soon they will [happen offline as well](https://forum.getodk.org/t/collect-coming-soon-offline-entities/46505). That means you could generate sequential IDs for two participants in the same household that you visit one after the other without connecting to the server in between. ---
**Author:** ![Ayoub\_Kakande](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ayoub_kakande/32/27358_2.png) [@Ayoub\_Kakande](https://forum.getodk.org/u/Ayoub_Kakande)\ **Post date:** [October 1, 2024, 8:06am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/7 "2024-10-01T08:06:26Z")
Thank you @LN very useful, would really appreciate a worked example of both ---
**Author:** ![LN](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/ln/32/1605_2.png) [@LN](https://forum.getodk.org/u/LN)\ **Post date:** [November 8, 2024, 1:06am UTC](https://forum.getodk.org/t/generate-sequential-id-based-on-pulled-data/49408/8 "2024-11-08T01:06:57Z")
So sorry about the delay in getting back to this! You can find @joybindroo's example adapted to use Entity Lists [here](https://docs.google.com/spreadsheets/d/1-Rga1aiFIX5RkGaHcT0aZq-YD7Kh7YSkryVmyNuwolc/edit?gid=1068911091#gid=1068911091). To try it out: - create and publish the form - go to the `households` Entity List add the "household" property. We need to add this one manually because the form doesn't save to it. - upload [households.csv](https://forum.getodk.org/uploads/short-url/nQwpaGQuzUWoIZ7miSos5Eqwkoi.csv) (99 Bytes) to the `households` Entity List ([docs](https://docs.getodk.org/central-entities/#importing-csvs-into-entity-lists)) - try the form out using a web form or assign the form to an App User to try it in Collect To see the id update within a single household, you will need to refresh the form in between adding new household members. If you'd like to see updates happen offline, you can try the latest Collect beta with Central v2024.2.0 following the instructions [here](https://forum.getodk.org/t/odk-collect-v2024-3-beta-offline-entities-counter-map-scale/49420/20). ### Example with household and participant Entity Lists You can find a more complex example using a participant Entity List [here](https://docs.google.com/spreadsheets/d/1TVSr8Z2ANRB1IwD9EmgKCCYZwpJHm24d7kotRWY-_Tc/edit?gid=1068911091#gid=1068911091). This example demonstrates doing a little bit more participant management by giving the option to archive a participant in addition to adding a new one. To try it out: - if you've been experimenting with the form above, you'll need to create a new project for this one because both have a `households` Entity List with a different structure - create the `households` Entity List ([docs](https://docs.getodk.org/central-entities/#creating-an-entity-list-in-the-central-interface)). This one isn't modified by the form so it needs to be created ahead of time. - add the following properties to the Entity List you just created: `village` and `household` - upload [households.csv](https://forum.getodk.org/uploads/short-url/aSgFEGFyZzPMTVqp9WatPunedCt.csv) (736 Bytes) to the `households` Entity List ([docs](https://docs.getodk.org/central-entities/#importing-csvs-into-entity-lists)) - create and publish the form - go to the `participants` Entity List that was created by the form, add the `village` and `household` properties - upload the [participants.csv](https://forum.getodk.org/uploads/short-url/v6o7DPIEUdkPYcWMWd9jztYyAok.csv) (7.1 KB) to the `participants` Entity List ([docs](https://docs.getodk.org/central-entities/#importing-csvs-into-entity-lists)) - try the form out using a web form or assign the form to an App User to try it in Collect ⚠ Some things to note: - The village and households lists feel like they might not be needed. They're convenient for specifying labels, but maybe the ids would be enough. This is the same issue that @TomJ described in [the idea to have a `unique`/`distinct` function](https://forum.getodk.org/t/add-distinct-function-for-use-with-entities/42945) - If you upload the household and participant CSVs as I created them, their order will look backwards in clients. This is related to [the idea to make Entity List sortable](https://forum.getodk.org/t/sorting-entities-for-use-in-choice-lists/47902) - There's a lot of computation going on in this form! I would want to try some test data with my target devices if I wanted to use this for more than 20,000 participants or so I know there's a lot going on in these examples! They were a good opportunity to illustrate several different Entities-related ideas. Hopefully you can take what you find interesting and leave the rest behind.