Calculations without a field

[Quartekoen]

Okay, here is a simple version of my problem. I have two fields on TableA, which are related to TableB. The Fields show:

TableB::number

TableB::date

What I want on TableA is basically: If TableB::date < Today's Date - 60, don't display the value of TableB::number. Instead, display 0.

Can this be done without adding a new calculation field? I'd much rather have it be the original field, just with the view formatted to show something different.

[LaRetta]

Goodness, it is difficult when you give abstract examples and not enough information but I shall truly try ... But first:

Can this be done without adding a new calculation field?

What is the first calculation you are talking about?

I have two fields on TableA, which are related to TableB. The Fields show:

TableB::number

TableB::date

No, fields in Table A are NOT TableB::number etc. Do you mean you also have identical fields in TableA with number and date? Please tell us the table names and purpose and exactly what fields exist in both that are related and what the 'number' represents. Otherwise we'll all just be spinning our wheels.

LaRetta :wink2:

[Fitch]

You could put a text block on the layout with "0" in it. Lay that over the number field and use conditional formatting to determine which one is visible (invisible meaning the text is the same color as the background).

[LaRetta]

Also, we need to know where and how you want it displayed. If there are many records in Table B and you want them displayed in Table A then they will probably be in a portal. If they do not fit the date criteria, then they won't display at ALL so displaying them as zero makes no difference.

If you want them displayed in Table B, then what does Table A have to do with it? I believe we need more information here ...

[Fitch]

I don't think it's really material where the fields are in this scenario, but my interpretation was that a layout based on table A is displaying fields from related table B. Not that complicated.

[LaRetta]

"Not that complicated."

Maybe not. And maybe it is. I asked for clarification; nothing more. Being too smug with answers will bite you when least expected. I would rather be clear on the need beforehand ... You apply your style and I'll apply mine.

[Fitch]

I just think you're making it more complicated than it is, LaRetta. Of course I could be wrong, happens all the time.

Although Quartekoen didn't specify that the records were in a portal, that does give me another idea:

You could set a global field to Get(CurrentDate)-60 -- maybe as part of your startup script -- and then create a relationship based on both the ID and TableB::date > gDate. That way only the newer date(s) would show.

[LaRetta]

Not a problem, Tom.

I considered several possibilities as well but chose to hold them until I had a clearer picture of the need (note my mention of a portal? It was to filter and only display the required records). I truly don't just spout fluff to confuse people. There are pieces of what was said which are illogical and open to different interpretation.

I do not believe a Developer should fill in those holes ... it is the responsibilty of the client to do so. It is our responsibility to bring up any gotchas and holes as we see them and to require clarity.

As I said, different styles ...

LaRetta

[Quartekoen]

Sorry about the long response time. I was away from Filemaker for the weekend

What is the first calculation you are talking about?

I don't have any other prior calculations, I was just referring to any brand new fields that I would have to create for this purpose.

[Raybaudi]

This isn't true because you can conditional format the field to have a fill color.

[Quartekoen]

This isn't true because you can conditional format the field to have a fill color.

Well, I've thought about this, and I think it is true. For example:

You have 2 pieces of plain text on the table: Text1 and Text2. Filemaker decides that Text1 is on top of Text2. If I conditionally format them, and Text1 becomes the same color as the background, then well, it's on top, and changing the fill color of either field will make no difference. I want to be able to see Text2, but filling the bottom text won't do anything, and setting Text1's fill will completely cover up Text2, since Text1 is on top.

[LaRetta]

there could be several 'number' and 'date' fields (perhaps a field for sales for each month [12 numbers and 12 dates], over several years), and this would require a relationship for each month.

No, honestly, you do NOT want to have 12 fields with numbers and 12 field with dates. You want ONE related table and use a portal here. If you have anything less than a 1-to-1 relationship with your other table then simply changing the format of the fields will not work - they will only always display the first record in the relationship.

You really need to think this through and take advantage of the Forum help before you box yourself in to a nightmare and you surely will be adding fields when you should be using records.

LaRetta

[Quartekoen]

Okay, I think we're starting to get off-topic Let's try this thing one more time with a different, yet very similar situation.

I have an item database, and a sales database. Lastly, I have a review table to show the sales of any particular item, broken down by month, and who it was sold to. Basically, the review table shows every order invoice that had this item on it, and shows in which month it took place. It lists all 12 months, each as it's own column, and each invoice as it's own row. If 100 of the item were sold in June, a new row would appear in the Review Table, and the number 100 would be listed under the June column.

Now, I'd like to be able to show the average sales for this item for each given month, which would be easily done if I were to make an Average field for each month, but I'd prefer it if I were just able to do the calculations and show the result without having to make 12 fields. Is this possible?

[Raybaudi]

Well, I've thought about this...

This uses the font size...

Quartekoen.zip

[comment]

Okay, I think we're starting to get off-topic

I'd say we're finally getting to the real topic here. :

[Fitch]

Conditionally setting the font size to one point is one way to go; another is to set the font size to gigantic, say 144 points. When you do that it will move outside of the field "frame" altogether. Works with normal fields or merge fields, in portals or not.