Linking Static Data to dynamic Data

[Chris_Bates]

Hello Appsheet Community,

I have an app that works off many google sheets, one of which is dynamically updated form a linnworks import. This sheet is usually updated daily in the morning before anyone gets to work. we then use this data to know what tasks we need to do through the day.

throughout the day we need to add about 6 pieces of information to these rows in the spreadsheet which we currently do by a deck view on the app. We edit the row and save the data. i have tried using v lookup on the sheets side of things with no avail. I also have tried using virtual columns for the added information but this doesn’t allow users to type in to fill out the fields.

the issue arises when the data gets refreshed as some of the rows could be removed from the dynamic side of the sheet but the static data persists. this then causes jobs to have incorrect information associated with it. I’m looking for a way of linking the static data to the dynamic side so that when the sheet updates it links the information to the correct row, and removes the no longer needed information.

To be more specific on the data and how it’s used. we have a list of phones that need repairing. each day, this changes as new phones get added and older phones get repaired. Some phones may stay in the list if they weren’t repaired that day but may change position in the sheet based on how many of that particular model we need. we need to add who repaired it, warranty sticker numbers inside the phone, day of repairing and the current status. all of which currently is static information that is added through the app and persists on the sheet causing a issue where the wrong phone has the repair date, warranty sticker, repair tech name and status.

I hope this makes sense and there is a solution.

Of course I will answer any questions I can.

many thanks,

Chris

[Chris_Bates · Accepted solution]

To anyone that stumbles upon this question,

we managed to solve this problem by having Linnworks export the data and append it to the end of the spreadsheet instead of replacing it. then using slices of that data restricted the ones without status’ so that new ones without a status are ones that need doing. the status was then updated to repair completed, repair failed, etc… this then allowed us to do what we needed. we just might need to clear the sheet when the list gets too large.