Group Items by Entity in Another Table

creoto

I currently have and endpoint the pulls together multiple tables into one API response using subpaths. An example of the response is below:

{

"id":146,

"created_at":1696558520736,

"property_id":0,

"user_id":0,

"status":"Created",

"payment_status":"Unpaid",

"first_name":"Peter",

"last_name":"Griffin",

"email":"peter.griffin@abc.us",

"phone":"555-555-5555",

"items":[

{

"id":10,

"created_at":1696558532615,

"proposal_id":146,

"service_id":1,

"property_instance_id":12,

"price":1200,

"property_instance":{

"id":12,

"created_at":1696558532342,

"property_id":24,

"type":"Residence",

"acquisition_method":"Purchase",

"purchase_price":111111,

"market_value":222222,

"depreciation":0,

"property":{

"id":24,

"created_at":1696558532044,

"user_id":null,

"address_1":"31 Spooner Street",

"address_2":"",

"city":"Quahog",

"state":"RI",

"postal_code":"02801"

}

},

"service":{

"id":1,

"created_at":1692830972637,

"name":"1031 Like Kind Exchange Analysis",

"description":"With respect to your proposed §1031 "like kind" exchange transaction, We will render the following "provided services" to you as part of this engagement:\n\n1. Provide an analysis that applies relevant tax law to the fact pattern that you provide. We will then provide our conclusions regarding:\n    - Whether the proposed transaction will meet the requirements for a §1031 "like kind" exchange; and\n    - If the transaction will meet the requirements for a "like kind" exchange, whether the transaction will generate any taxable "boot," rendering the transaction partially taxable.\n2. Provide you with information regarding when the various steps of the "like kind" exchange must be completed.",

"pricing":"Fixed",

"price":1200,

"min_price":0,

"percentage":0

}

},

{

"id":11,

"created_at":1696558532856,

"proposal_id":146,

"service_id":2,

"property_instance_id":12,

"price":0,

"property_instance":{

"id":12,

"created_at":1696558532342,

"property_id":24,

"type":"Residence",

"acquisition_method":"Purchase",

"purchase_price":111111,

"market_value":222222,

"depreciation":0,

"property":{

"id":24,

"created_at":1696558532044,

"user_id":null,

"address_1":"31 Spooner Street",

"address_2":"",

"city":"Quahog",

"state":"RI",

"postal_code":"02801"

}

},

"service":{

"id":2,

"created_at":1692831002449,

"name":"Taxation Services - Audit Protection",

"description":"Up to $10,000 in Audit assistance related to this real estate transaction\n\n\nWorry Free IRS/State Audit Representation\n\nTo assist you in responding to IRS or state tax agencies who are auditing your tax returns. This includes:\n\n- Reviewing the notices\n- Recommending the best course of action and response\n- Communicating with tax agencies and responding to notices and requests\n- See Audit protection service for more details",

"pricing":"Percentage",

"price":0,

"min_price":0,

"percentage":3

}

}

]

}

My ideal situations would be to have a response where the top-level objects in the items array are grouped together under their associated property. The resulting JSON might look like this:

{

"id":146,

"created_at":1696558520736,

"property_id":0,

"user_id":0,

"status":"Created",

"payment_status":"Unpaid",

"first_name":"Peter",

"last_name":"Griffin",

"email":"peter.griffin@abc.us",

"phone":"555-555-5555",

"items":[

{

"property_id":1,

"address_1":"31 Spooner Street",

"address_2":"",

"city":"Quahog",

"state":"RI",

"postal_code":"02801",

"services":[

{

"id":1,

"created_at":1692830972637,

"name":"1031 Like Kind Exchange Analysis",

"description":"With respect to your proposed §1031 "like kind" exchange transaction, We will render the following "provided services" to you as part of this engagement:\n\n1. Provide an analysis that applies relevant tax law to the fact pattern that you provide. We will then provide our conclusions regarding:\n    - Whether the proposed transaction will meet the requirements for a §1031 "like kind" exchange; and\n    - If the transaction will meet the requirements for a "like kind" exchange, whether the transaction will generate any taxable "boot," rendering the transaction partially taxable.\n2. Provide you with information regarding when the various steps of the "like kind" exchange must be completed.",

"pricing":"Fixed",

"price":1200,

"min_price":0,

"percentage":0

},

{

"id":2,

"created_at":1692831002449,

"name":"Taxation Services - Audit Protection",

"description":"Up to $10,000 in Audit assistance related to this real estate transaction\n\n\nWorry Free IRS/State Audit Representation\n\nTo assist you in responding to IRS or state tax agencies who are auditing your tax returns. This includes:\n\n- Reviewing the notices\n- Recommending the best course of action and response\n- Communicating with tax agencies and responding to notices and requests\n- See Audit protection service for more details",

"pricing":"Percentage",

"price":0,

"min_price":0,

"percentage":3

}

]

}

]

}

I'm not sure the best approach to transform the response is.

Max

im not sure of i understood situation correctly. but if i did - try using addons to display information from other tables, and then go to addon settings and adjust sorting rules per addon there.

Ray Deck

Two approaches:

1. Using addons and flipping the orientation (e.g. start with a list of properties and "add on" the transaction. This might work to a more or less limited extent depending on how your tables are structures.
2. Get the data from the tables by whatever means, then loop over the fetched data to restructure it. The function stack is where Xano really earns its pay. Put it to work to make the data you want to serve to your customer.

We work on data transformation in the function stack quite often as part of working on the hardest 5% in State Change daily office hours.

creoto

Thanks for the responses @Max and @Ray Deck. I'll play around a bit more with the addons (not subpaths 🤦‍♂️) a bit more. I think I'll probably have do more in the function stack. I started down that path but didn't go to far.