The possible real fixes are either to section off your water circuits in summer, and only run the properly cold water through FCUs, wall or ceiling loops.
Or to get a multisplit heat pump, use a normal refrigerant-air indoor unit to cool and a refrigerant-water indoor unit to heat the water loops.
In that scenario, are FPUs a good option? Are there other drawbacks?
Even then, there is a fair limit in cooling and efficiency you can get from them because of the lack of phase changes occurring.
If you have a balanced mechanical ventilation (both inlet and outlet) that would be one of the best options to condition the whole house without putting air conditioning units everywhere. Having said that, we turn on the air conditioning in the attic during hot days which removes the biggest heat and moisture from the house. Next up is installing more sun blinds to prevent the heat from entering in the first place.
You just have the added equipment and maintenance for the water/glycol loop vs an air to air system that only pipes refrigerant around. Fan coil units or VAV boxes are the usual terminal units for commercial HVAC systems that have a chilled and hot water loop, VAVs are used in a system with ducted AHUs and FCUs are used for apartments (stacked FCUs with vertical supply/return risers) or spaces not served by ducted air handlers for whatever reason.