Grab the contents of a particular field without relationship

[ckai]

I can't for the life of me work out how to do this. Essentially I need a lookup that returns the value of one particular field. I have a table of rates. Most of the rates are related to another table, but one particular one can't be since it shouldn't be selected. It's essentially a storage record so it can be easily changed if needed.

At the moment this rate is hard-coded within the script that generates the data for invoicing.

What I want to do is be able to retrieve the rate instead of having it hard-coded.

Kinda like this: Lookup ( Rate ; where Code = "DSL"). That would be nice haha.

Anyways to get around this? I could throw this rate with a global table but then all my rates aren't together.

Basically, all the other rates are related to an items table but the "DSL" rate is kinda related to the client.

Thanks all

[comment]

all the other rates are related to an items table but the "DSL" rate is kinda related to the client.

What does "kinda related" mean?

[Vaughan]

Is this some kind of default value? Sounds as though it should be in a preferences table. Search the forums for more info.

[David Jondreau]

Is this a value that's specific to a client record or is it the same across all records? How easily does it need to be changed and by whom? You could store the value in a Custom Function and call that.

[comment]

You could store the value in a Custom Function

You could - but you most definitely should not. Data is in the user province and should be modifiable without requiring developer's privileges.

[ckai]

What does "kinda related" mean?

The rate is a funding amount, which gets deducted from some clients invoices (if they are entitled to the funding) and then gets charged to the particular client "DSL". I say "kinda related" because at the moment we only have "DSL" providing this type of funding, essentially a large discount. I would like to make it easier to add other funder clients in future though. At the moment I have a separate DSL script, which as mentioned in my original post, has the funding rate hard coded. It's not user editable like all the other rates.

Is this some kind of default value? Sounds as though it should be in a preferences table. Search the forums for more info.

Yeah I suppose it is a default value. I'm trying to get the rate into my "rates" as opposed to a overall preference table. That way if another funder comes along we can just add "XYZ Funding" to the rates table as well.

Is this a value that's specific to a client record or is it the same across all records? How easily does it need to be changed and by whom? You could store the value in a Custom Function and call that.

The rate is specific to the one client that provides funding. I could create a field in the clients table for "funding amount" but that field will be empty for all apart from 1. Hence my logic in throwing it in the rates table.

The rate can only be changed by going to the actual script. Certainly not user friendly.

The reason I'm looking at this now is the difficulty in editing the rate was brought to light when we found out the rate had actually changed!

[comment]

I got somewhat lost in your explanation. If you want to your invoice to have a client-based discount, the discount rate should be in the Clients table and looked up from there. II am not sure what this has to do with the other "rates".

[ckai]

To be honest I'm not sure why I didn't explain like this before:

"DSL" is a client that provides funding to other clients. They have a maximum amount they will fund per eligible client. Say Mr X has an invoice for $500. They are also allowed DSL funding. I have a script that works out how much Mr X is allowed in DSL funding. Say Mr X's max is $200 (2x DSL Funding rate of $100). So Mr X gets a total invoice for $300 and "DSL" gets an invoice for the amount they funded, $200.

Mr X's invoice would look like this:

Description Quantity Rate Total

DSL Funding 2 -100 -200

Services 1 500 500

Total 300

"DSL"'s invoice would just state who the DSL Funding was provided for (Mr X) and of course would not be a total that got subtracted.

So Mr X essentially gets a "discount" but it appears as a rate. The 'quantity' amount for DSL Funding (the maximum the client is allowed) is determined by how many days of service we provide a client per week.

At the moment, I have a "DSL Funding" check box on the client screen. One of my invoice scripts searches for all the clients that have this check box selected and loops through each client determining their maximum "quantity" amount. This quantity then gets multiplied by the DSL Funding rate which is hard coded in the script and the part I want to make more user editable.

Does this help?

When designing the database this was the most confusing part to find out how it works and to apply. So there may well be a very simple way I should do it but my brain is pretty much in complicated mode every time I look at it. I was thinking putting the DSL Funding rate in the rates table was going to be the simplest .

[comment]

Does this help?

Not quite. Who/what determines the "discount" (rate and quantity) - and no less important, when?

I still have the feeling that these discounts should be handled separately from the invoice's line items. Something like coupons comes to mind - but coupons are a method of payment - not a product or a service to be purchased.

[ckai]

Not quite. Who/what determines the "discount" (rate and quantity) - and no less important, when?

I still have the feeling that these discounts should be handled separately from the invoice's line items. Something like coupons comes to mind - but coupons are a method of payment - not a product or a service to be purchased.

The general criteria for the funding is determined by "DSL". They tell us the maximum they will pay per week and what their rate per day is. Their rate is actually on a per day basis. At the moment they will fund a maximum of 2 days per week. However, if we've only provided a client 1 day of service, then DSL Funding will only be 1 day. The funding they provide doesn't actually pay for a full day of service though.

If we have more Funders come on board like DSL, they will determine their own criteria (maximum amount and rate). We do have another similar client that essentially provides "funding" but they will pay for an entire day of service so all we do is charge them for the whole day. Since DSL Funding doesn't cover all our charges for the a whole day of service we provide, we show it on the invoice as a deducted amount.

As to your "when" question, I'm not sure what you mean. If you mean when the amount it applied, it's during an invoice run. If you mean when are we told the amount that will be funding for, this is doesn't change and stays the same for each client. What determines the amount of funding I suppose, it how many days we provide service to the client (Mr X). For example, we provide 4 days in one week, Mr X will get 2 "days" funding. We provide 2 days in a week, Mr X will get 2 "days" funding. We provide 1 day to Mr X and he'll get 1 "day" of funding.

Of course, DSL's maximum amount of funding they provide could change in the future (like what has occurred with their rates), but since it's been like that for about 10 years now, it's something that can be hard coded in my mind.

I kind of see your point with respect to not showing the funding as a line item. We don't actually "add" the funding amount as a line item as such. It's generated at time of invoicing, automatically. The "DSL script" calculates and adds it to an XML file (along with the other charges for our clients) which we import into our invoice program.

[comment]

If I understand correctly, you have clients (people to whom you provide services, like Mr. X) and sponsors who finance some of the costs (like DSL).

My question is this: if I had to write out an invoice manually, how would I know the amount of funding that should be applied, from which sponsor/s?

There must be a place where each sponsor's policy is recorded, so that it can be looked up. Depending on how complex a policy can be, this could be the Sponsors table, or a separate table (e.g. when a sponsor can have different policies for different types of client).

[ckai]

Good way of looking at it. That's exactly right.

How would you know? You would ask the boss who keeps all of that stuff in her head! haha. Seriously, this is actually what happens.

We don't have that information recorded. If we were to record it we would simply add it as a note for the "sponsor".

The "sponsor/funder" that provides funding for the FULL day we have summary portal which shows who will be paying for what days. They provide for 24 hours/week (3 days) but are more flexible (they'll increase funding if they feel they need to). This criteria is shown in a label beside the summary portal. Because of the flexibility we apply this manually (we select them from a drop down box under a "Charge to" field).

The Funding table idea is certainly an interesting one. It would take some serious thinking about structure since some funders/sponsors provide day amounts that partly cover our charges (DSL) and others provide hourly amounts that cover our charges for a full day.

I certainly never thought about doing it this way, I suppose mainly because my brain automatically put it in the "too hard" basket :)

[comment]

Well, it's not going to be easy if each sponsor is allowed to make up their own rules (and with them being sponsors, I'd guess they will be allowed anything). Still, if you want this to be automated then the information needs to be transferred from the boss' head into the solution.

[ckai]

I suppose I'll see what I can come up with. Should be fun ("the sarcasm is strong in that one" haha). At least I won't have a problem with unrelated data with a funding table...I hope!

Thanks mate for your help. I'll see how I get on.

[Vaughan]

The most flexibility can be provided by having a field where an expression can be typed that is then evaluated.

[ckai]

I sort of get this Vaughan, but could you elaborate or give me an example. I'm in graphic-design-mode looking at icons :)

[Vaughan]

It is a text field that contains a formula that then gets "run".

For instance, I had a client with complex reporting requirements where items had to be re-categorised frequently. So I made calculation:

Field = Report_Type:

Case(

Type = "television" ; "electronic media" ;

Type = "web site" ; "electronic media" ;

Type

)

And then another field in the reports module was:

Evaluate( Report_Type )

[comment]

It's a flexible method all right - but it should be used only as a last resort (not sure your example meets this criteria). It is the opposite of hard-coding: it places a schema element in the data domain. If you clone the file, you will have lost a part of your schema. It's not the fastest method, either.