I am hoping one of you brilliant, more experienced administrators can help me wrap my brain around a problem. We provide group homes for autistic and mentally handicapped people. There are usually 3 clients assigned to a home and we provide 24 hour care including paying their monthly rents and utilities.
In order to assign accurate rents and utility costs for a house I need to know who was living in the home at any give time so we can allocate costs correctly. It's not complicated I just need a "move in date" and a "move out date" for each client in the home and I need to be able to look back through the whole history of the house to determine who was in the house as of a given month.
In Salesforce I have 2 objects. I have a custom object called "House" and a person account called "Client". A client is in a lookup relationship to a house. Is there a way to keep a history including a custom date (since the data entry might not happen on the date that the move occurred) when people move in and out.
This is what I have done so far.
On the Client object I am tracking Field History on the "House" lookup field and history tracking on "Date of Transfer" field.
In order to force both fields to change at the same time I have a pop up that requires both fields so anytime a client is assigned to a new house it records the house and the date of transfer on the Client Object.
This works well to see where a client has lived. But when paying rents and assigning costs we need to know who was in the house for that month.
I have tried to replicated this information on my "House" object by:
Including a "ClientHistory" Model on my Skuid page that pulls data from "Account History".
I then and then add a "Model Condition" to only show me "Old Value" that = Url Parameter of the House ID. and "New Value" that = URL Parameter of House ID since the House ID is listed in the account history.
I thought this would solve the problem but I get a Red Bar and a warning saying I could not perform queries on Old Value or New Value on Account History.
Can anyone think of any other work around or sound set ups? (Thank you for reading this long post, I give you Kudos for finishing it! :))
Dealing with history tracking can be tricky. I probably would have done it without tracking by skipping the lookup from client to house and instead used a third object named Assignment that had lookups (or m/d) to both house and client plus fields for start and end date. So each continous stay for a client would mean an entry into assignments. Should be pretty easy to both visualize in skuid an report on.
Ok I can change it, so I create an Assignments object. And I create Lookups to Client and House and I have a move in date and time. At that point that is a snap shot of the current move. I.E. John Doe, Colorado House 01, 01/01/2014 10:00 AM
When I create a new record that gives me a running history of the Client, that all makes sense.
John Doe, Colorado House 02, 02/01/2014 11:00 AM
Table View looks like a History: John Doe, Colorado House 02, 02/01/2014 11:00 AM John Doe, Colorado House 01, 01/01/2014 10:00 AM
The tricky part (in my brain) that I did not explain is I need the Client record to derive information from the House record Assignment. I.E. The client’s address and phone number is a formula field that references the House’s address via the lookup.
So I need their to be only one valid house assignment at a time, but I need to track the history on both a House Object view (via Skuid) and on the Client object view (via Skuid). I.E. when I am on the Client record I need to see all John Doe’s transfers from house to house. When I am on the House object I need to see all the client’s that have transferred into and out of that specific house.
This is probably a simple thing, but my brain keeps stalling out on the history side because I need the current house assignment to be the most recent Assignment record so I can reference fields on the object. I have tried creating Workflow triggers that give me a PriorValue of the house. That gives me a history of the Current House and the Prior, but I need a history of all transfers. I appreciate your suggestion Peter, I think it is a good one, I just don’t know how to have a current house assignment derived from a record.
In writing this response it seems like I need the Assigment object to automatically create a New Record anytime the existing lookup field on the Client object is changed? With triggers to copy the old and new House to the record? It seems like that is what field history does? Is there a better way or more efficient way? Thanks for your response and time! Any suggestions to get me to the answer are appreciated.
If you use Master-Detail relations between Assignment and Client/House you can use cross-object workflows to ensure only one current house per client and you can also use workflows or roll-up summaries to get info to the parent objects. Things like.
When new assignment records are created update Client checkbox field “in housing” to checked On move out date entered on assignment records uncheck that checkbox Use a validation rule to ensure no new assignment records where client has that checkbox checked.
Or you can use a roll up summary on Client (Max-MoveIndate) with a filter of (MoveOutDate = null) to get the latest move in date for the Client A roll up summary of (Count) white the same filter would give you number of current housings (1 or 0) and could also be used for validation rules.
In reality you probably have a lot more to take into account but a combination of cross-object wf/formulas, validation rules and roll-ups can probably solve most of your needs and then that data structure will work well both for standard reporting in SF, standard tabs/detail pages and for SKUID pages. With SKUID you could failry easily do a very nice UI for handling booking based on this structure.
