Complete loss - relational db's

[kiwiora]

Hi guys,

-P.S this is coming out in double paragraph spacing, don't know why sorry - makes it look more long winded than it already is

I am attempting to create this database, currently the user uses a multitude of separate excel spreadsheets to monitor company. Please tear it to shreds, I have basically made it up as I have gone along and with the hour of night, I have probably not thought through some issues. So constructive critiscism welcome Sorry for the long winded post, but i always found it better to explain first what i am trying to achieve!

P.S haven't really done much to layout so buttons may be dodgy.... have checked most of them though!!

The Plot:

This database tracks sales, clients, agents and subsequent accounting.

The Scenario:

Create a client

Create a Sale - add client code

- add agent

- add sale amount

Create an account (if ezypay i.e instalment payments) - add ezypay

The Issues:

1. I have looked at this for too long obviously. I can not get the Agents name to appear in the Ezypay record. My relationship must be up the spout, but I can't figure it out. Perhaps it is too circular? the purpose of the agents name is to then calculate commissions on payments. But perhaps I should do a separate relational db to calculate commissions and respective agent. This may then solve issue 2.

2. Commissions - worked out on ezy pay - must add lump sum deposit too - currently only calculates ezypay payments. Was trying to do work around by selecting radio to choose dep or ezypay, However then had issues with calcs in glamourshots calculating correctly. Perhaps separate relational db as specified in 1 above?

3. report - have got it to sub-total by month, i need it to sub-total by month then sub-sub-total by agent

i.e

MARCH 2003

Allison

• {entry}
  
  {entry}
  
  {entry}
  
  
• {allison total}

Freddy

• {entry}
  
  {entry}
  
  {entry}
  
• {freddy total}

{march total}

APRIL 2003

Allison

• {entry}...

and so on...

I had a go at the sub-summary gizzo but could not get it to work appropriately. Am also thinking of creating some sort of script with set dialog where user can specify month (range perhaps?) and/or agent to generate report.

4. Brain Strain

I am trying to nut out how to deal with the whole commissions bit. Do I want to create some sort of check box field to Y when commission is paid? so that is has the facility to deal with processed payments, on x date and y cheque?? not sure about this one.. maybe less is more

I think that's all for today Appreciate any advice.

P.S I went to "intermediate - advanced" filemaker pro training today thinking i might actually learn something i don't know. Dillusional!! figuring it out myself and learning from posts on here (and the odd asking you to help out completely) has given me the best training!!

I might not understand all that you guys post, but it puts ideas (good and bad) in my head to try new things. So a big thank you!! I know I understand and am far more creative than I was at the start.. even ended up training the trainer on some cool hints and tricks picked up on here.

Aussies need to spread the word on filemaker to create more of a demand for it.. is sooo difficult to find any training on it... worth while attending that is

help.zip

[kiwiora]

Ok a few coffees/late nights and have worked out everything anyway. Dunno if i've done it "correctly" but it all works.. yahhh

I would delete the post (since no one replied anyway) but it won't let me as it's been sitting there for too long.