solutions wanted Help Wanted - IT Service Management Database

[danielrmitchell]

First off, I am sorry if this is being posted in the wrong place - I tried searching for the right place.  Admin, please place in correct board if necessary.

 

Background - I have worked as a self-employed Computer Repair Shop for some 5 years now and have used an Access database, purchased pre-made online and made it fit my needs as a customer, device, service and invoice tracker.  However as I use Mac's primarily I am having to run the DB "Service Manager" through Windows via Parallels virtual machine which is less than ideal.

 

As such I have been working on, for the past year, my own Service Manager in Filemaker.  I say for the last year, due to other priorities I pick it up and build a little every now and then, sometimes with months in between.  I have started, restarted, given up and started again many times.  Now it is becoming more and more necessary to get this sorted.

 

The help I am wanting is from someone who is prepared to occasionally hold my hand and pop up with suggestions on a semi-regular basis until I have this sorted.  I am fairly confident in my skills, but need clarification from time to time.

 

Here I will describe my db and the workflow I expect it to handle.

 

A Customer can have many Devices (Computer, Mobile Phone, Tablet etc)

A Device can have many Services

A Service can have one Invoice

An Invoice can have several LineItems

 

I would like to record when a Customer is created and whenever a Customer details are modified (done)

I would like to do the same with each Device (done)

I would like to only display Services for the related Device (done)

I have not started work on the Invoices or LineItems yet.

 

So  the workflow goes something like this

1. Customer is created (or selected)
2. A Device is created (or selected)
3. A device is Booked In (part of Service) and details are printed (Containing details from Customer, Device and the Booked In) as a form, with Terms and Conditions for customer to sign (this should then be scanned and added to the Service in a Container)
4. A device is worked on and service notes recorded (part of Service)
5. Parts used (LineItems) are recorded during the Service
6. A return form is printed (Containing Customer, Device, Service) and signed, then scanned to another Container
7. An Invoice is produced (Containing details from Customer, Device, Service, LineItems)
8. An Invoice is then marked as paid with the date recorded and the payment type (Cash, Card, BACs, Cheque etc)

There are other parts to the workflow that I have not described, but this core functionality is what I need for now.  I am sure that once I have this pinned I can add the rest later myself.

 

Attached is what i have produced thus far on this attempt.

I think I have become somewhat confused in my production!

 

 

SM_Alpha_1.fmp12.zip

[john renfrew]

from the file you seem to be in the UK. Right?

 

which version of FileMaker?

 

suggestion 1 is that an iPad with Go will help reduce the paperwork and then customers can sign on the screen instead of scanning in bits of paper..

 

 

 

john

[danielrmitchell]

Thanks John, I am in the UK, you are absolutely right.

 

Going down the iPad route is definitely going to happen.

 

At the moment though, I have some 9000+ signed work orders already, which will have to be added to the finished db when I start adding customer, device and service details so my primary concern is to get core functionality up and running.

[LaRetta]

The help I am wanting is from someone who is prepared to occasionally hold my hand and pop up with suggestions on a semi-regular basis until I have this sorted.  I am fairly confident in my skills, but need clarification from time to time.

 

Hi Daniel,

 

I'm unclear whether you are asking for free occasional assistance or paid assistance but it appears all you need is to bolt on an invoices/lineitems piece.  Below is your graph with the tables you would (probably) need and I suggest you download this demo file:  http://fmforums.com/forum/topic/63425-auto-fill-one-field-with-text-from-two-fields/#entry300150.  In your existing request then, a Service IS an Invoice.  Even if the service ends up being a no charge, an invoice is still created. 

 

This forum is a great place to ask questions and folks here who respond are very nice!

[danielrmitchell]

Hi LaRetta, thank you for your input.

I am looking for free occasional assistance, as I believe I do have the capacity to do this myself (and I really want to) but I am simply struggling with some parts.

I will download the demo file you linked and have a look.

 

Thank you

[danielrmitchell]

Referrals

I have Customers who have been referred by other Customers.

I would like to record which new Customer was referred by which old Customer and also how many new Customers have been referred by the old customer.

Finally, based on the number of referrals made a Customer is entitled to a discount - this bit is easy as 1 Customer referral = 1% discount, however can the amount of discount be capped at 20%?

 

My understanding of how this would work goes something like this:  I make a new table - Referrals consisting of 

• ReferralID (PK)
• CustomerID (FK)
• CustomerFullName <---Not sure if needed in this table

I am also pretty sure that I need need to create a second instance of Customers table so that I can get it to look itself up?

 

Anyone help with this?

[comment]

I am looking for free occasional assistance,

Then just post an occasional question in one of the forum's sections as appropriate - that's what it's all about. This section (Classifieds) is for people seeking products & services for hire or sale.

 

Referrals

I have Customers who have been referred by other Customers.

I would like to record which new Customer was referred by which old Customer and also how many new Customers have been referred by the old customer.

 

Assuming a new customer is referred by at most one existing customer, just add a ReferredByID field to the Customers table and use it to define a self-join relationship:

 

Customers::CustomerID = Customers 2::ReferredByID

 

where Customers 2 is another occurrence of the Customers table. This will enable you to see the customers referred by the currently viewed customer.

 

If you also want to see who referred the current customer, you will need either another self-join in the opposite direction, or a calculation field (or fields) to get that data. Alternatively, you could just format the ReferredByID field as a popup and let it show the referrer's name through the value list.

 

based on the number of referrals made a Customer is entitled to a discount - this bit is easy as 1 Customer referral = 1% discount, however can the amount of discount be capped at 20%?

 

The discount rate can be calculated as =

Min ( Count ( Customers 2::ReferredByID ) ; 20 ) * 0.01

The result type is Number, and the context from which to evaluate this calculation is Customers.

[Lee Smith]

Hi LaRetta, thank you for your input.

I am looking for free occasional assistance, as I believe I do have the capacity to do this myself (and I really want to) but I am simply struggling with some parts.

I will download the demo file you linked and have a look.

 

Thank you

Please separate your questions and post them in the correct  topics areas.

 

This topic area is restricted to 

 

Searching for that ready made solution, perhaps someone else can point you in the right direction

 

 I see  comment  has already said this above.

[danielrmitchell]

Thank you comment, I have created the relationship you suggested ( Customers::CustomerID = Customers 2::ReferredByID ) which has placed a ReferredByID field on my layout, I have edited this from a text box to a popup menu using a value list as in the screen shot below

 

https://www.dropbox.com/s/mpke51uw6akcax2/Screenshot%202014-11-21%2015.19.04.png

 

However when implemented as such I have both the CustomerID and Full Name displayed.
When I choose instead to Show Values only from Second Field the popup menu displays only "?", and if I change to Sort Values using: Second field instead I get "1 ?" (CustomerID followed by "?").

Obviously I would rather display only the FullName, sorted alphabetically.

Any ideas?

[LaRetta]

Hi Daniel,

 

Check the cFullName calculation's Options then select Storage Options.  Uncheck 'Do not store calculation results'.  There should be no need for this calculation to be unstored unless it references a global field, another unstored calculation, or a related field.

[LaRetta]

Wait a sec ... FM would have told you the value list would not work if the cFullName was unstored!  I bet it is that your cFullName calculation is result of NUMBER by accident!

[danielrmitchell]

LaRetta! Spot-on, thank you.

I had indeed made the calculation result a Number (which is, I believe, the default result for a calculation field?).

Changed to text and it is now working perfectly.  Thank you.

[danielrmitchell]

Lee Smith although I can see where you are coming from with your recommendation to post individual questions in specific areas of the boards, I do think that I am posting properly.

 

My request is for assistance with the PROJECT and as such, I would find it to be of greater assistance to me if all information was kept in a single thread for ease of reference and also for anybody else should they be entertaining a similar project themselves.

 

Baring this in mind, should you insist that I separate everything out into separate threads I shall.

[LaRetta]

Hi Daniel,

 

Everyone is working on a project.  But these projects have pieces or problems which use FM tools to solve them, such as portals, popovers, calculations, imports ...

 

So this forum is set up so each tool has a forum section.  This separation by the tool is more helpful otherwise, your single thread would (or could) go on several pages.  And if someone else had a single problem having to do with a summary field in a report and you had the same problem, they would need to read potentially many pages of our discussions before they got to that section.

 

By putting a clear subject in an individual thread and starting that thread in the forum with the tools or methods indicated, other folks can search that forum section and quickly find related information to assist them.  Some threads on an individual problem can go on for pages all by itself.

 

So yes, it is important that you start new threads for different problems within your project.  When you wish to review your threads, upper right corner of the forum is your name.  You can pop that open and select 'my content' and see all your individual threads.  :-)

If you do not know the correct section to post a question, i.e. you don't know what tool to use to solve a particular problem, then post in what you think is the best one.  Moderators here can move it for you if need be.  Lee is very helpful with helping to keep these threads organised on this forum!

[danielrmitchell]

Thanks LaRetta, I appreciate your input there and see your points are most valid.

I am not much of a forum user and I guess I forget how they work sometimes!

 

I shall follow the advice your and Lee have given and post questions in the proper manner.

[Lee Smith]

Hi Daniel,

 

Everyone is working on a project. 

Well said.

 

I shall follow the advice you and Lee have given and post questions in the proper manner.

Good answer.

 

Lee