Advice on parent/child records and/or data structure

[Jay1]

Hello everyone. First off I’d like to say thank you to the community for already offering me so many valuable answers to questions I’ve had. However, I’ve become stumped on how to structure my data and thought someone more experienced may be able to help. Apologies for being a noob!

I have three main tables in my Field Service app:
Equipment - key: Equip Serial
Components - key: Barcode
Service Report - key: Visit ID

Whereas the Service Report only exists/makes sense with a related piece of Equipment & its corresponding Components. I’d like to have ref rows from the Service Reports appearing under both the related Equipment records as well as the related Components.

I’ve achieved that quite easily, but what I’m struggling with is our workflow. Our techs need to be able to change out certain Components within the Equipment on the fly; a lot of times they don’t know which Component they may need to replace until they’re very far into a detailed Service Report. So the only solution (that I’ve thought of) is to have the Component table as the child to the Service Report table, in order to allow for edits on the fly from within a form. However, if you do that, it doesn’t make sense in the overall data structure described above.

Any advice would be very appreciated! Sorry if I’m missing any key concepts or anything, and please let me know if you need more detailed/specific information on anything. Cheers!

[Ethan_U]

I’d like to try to help if you can help me understand better.

Basically I’m imagining you have a listing of different types of equipment of a standard specification or model type and then you have multiple physical instances of those models of equipment in your facility? And every one of those pieces of equipment in your inventory of the same model has the same design and therefore the same parts?

Unless I’m not understanding your use case enough then please let me know.

[Jay1]

Hey Ethan, thanks for the reply.

You’re almost there, yes. The Equipment is of different model types, and the Components are parts bought that are housed inside the Equipment, and of a different type (pump, meter, etc). Also a bit of a kicker in this equation is the fact that we need to be able to “track” Components as well our Equipment, or in other words see a history of where those Components have been (either inside a piece of Equipment or at one of our facilities). Just to elaborate a little more on top of that, each piece of Equipment (if in operation) is at a Customer site/location.

And as I said in the OP, what I’m struggling with is allowing the ability to edit those child Component records inside a Service Report, which is somewhat necessary given our workflow: get called out for a specific problem, but may not know if a part needs to be replaced until they’re in the middle of this fairly lengthy form. However, if I structure the data that way, the overall structure doesn’t make sense, as the Service Report is now the parent of the Components instead of the Equipment. So I think there may be a slice way of doing this or something that I’m just missing, unless this type of workflow scenario seems unachievable for some reason.