Extended filter function of date and condition

Sven_Meissner · December 29, 2020, 6:56pm

I am new and excuse my limited english. I’m building an inventory management system for vehicles. There is an Inventory table “Bestand” with columns
[Source],[Date-1.EK],[Date-2.EK],[Date-EK],[Source2][Date-1.VK],[Date-2.VK],[Date-VK],

I have a filter with a start and end date. I would like to filter the date in the period and show lines depending on “source”. My structure: FILTER (“inventory”,
AND ([Date-EK]> = [Filter-Start], [Date-VK]> = [Filter-Start], [Source] = “Other”,
OR ([Date-EK] <= [Filter-End], [Date-VK] <= [Filter-End], [Source.] = “Other”
)))

I get no further to determine that all dates are seen sorted and compared to “source”. my simple formula is too inadequate and I couldn’t find anything suitable in the help. I am really grateful for any help.

LeventK · December 29, 2020, 7:53pm

@Sven_Meissner
Ihre Formel ist semantisch nicht korrekt. Bitte versuchen Sie es damit:

FILTER(
	"inventory",
	(AND(
		"Other" = [Source],
		OR(
			AND(
				[Date-EK] >= [Filter-Start],
				[Date-EK] <= [Filter-Start]
			),
			AND(
				[Date-VK] >= [Filter-Start],
				[Date-VK] <= [Filter-End]
			)
		)
	)
)

Sven_Meissner · December 29, 2020, 8:30pm

thank you Levent for your help, but it doesn’t work yet. I would like to explain the structure to you again. [Source] and / or [Source2] have as one of the values "other" for which the data records are to be filtered by start and end date. So the value “Other” is in one of the two columns or in both at the same time. All date columns should be checked and filtered according to the start / end date. All date columns are: [Datum-1.EK], [Datum-2.EK], [Datum-EK],
[Datum-EKF], [Datum-Zuzahlung], [Datum-1.VK], [Datum-2.VK],
[Datum-VK], [Datum-Auszahlung], [Datum-Ablöse], [Datum-1.Absch.], [Datum-2.Absch.], [Datum-3.Absch.], [Datum-4.Absch.], [Datum-5.Absch.].
So there is a date in the search area and "Other in (source) or (source2) or both, the line should be displayed. Thank you very much for your help and I’m really excited about Appsheet and the clever community

LeventK · December 29, 2020, 9:21pm

@Sven_Meissner
I have constructed the expression as per your given initial requirements in post#1. You need to list and filter all your date columns in the expression as per your needs inside the OR booelan:

FILTER(
	"inventory",
	(AND(
		OR(
			"Other" = [Source],
			"Other" = [Source2]
		),
		OR(
			AND(
				[Date-EK] >= [Filter-Start],
				[Date-EK] <= [Filter-Start]
			),
			AND(
				[Datum-1.EK] >= [Filter-Start],
				[Datum-1.EK] <= [Filter-End]
			),
			AND(
				[Datum-2.EK] >= [Filter-Start],
				[Datum-2.EK] <= [Filter-End]
			),
			AND(
				[Datum-EKF] >= [Filter-Start],
				[Datum-EKF] <= [Filter-End]
			),
			AND(
				[Datum-Zuzahlung] >= [Filter-Start],
				[Datum-Zuzahlung] <= [Filter-End]
			),
			AND(
				[Datum-VK] >= [Filter-Start],
				[Datum-VK] <= [Filter-End]
			),
			AND(
				[Datum-1.VK] >= [Filter-Start],
				[Datum-1.VK] <= [Filter-End]
			),
			AND(
				[Datum-2.VK] >= [Filter-Start],
				[Datum-2.VK] <= [Filter-End]
			),
			AND(
				[Datum-Auszahlung] >= [Filter-Start],
				[Datum-Auszahlung] <= [Filter-End]
			),
			AND(
				[Datum-Ablöse] >= [Filter-Start],
				[Datum-Ablöse] <= [Filter-End]
			),
			AND(
				[Datum-1.Absch.] >= [Filter-Start],
				[Datum-1.Absch.] <= [Filter-End]
			),
			AND(
				[Datum-2.Absch.] >= [Filter-Start],
				[Datum-2.Absch.] <= [Filter-End]
			),
			AND(
				[Datum-3.Absch.] >= [Filter-Start],
				[Datum-3.Absch.] <= [Filter-End]
			),
			AND(
				[Datum-4.Absch.] >= [Filter-Start],
				[Datum-4.Absch.] <= [Filter-End]
			),
			AND(
				[Datum-5.Absch.] >= [Filter-Start],
				[Datum-5.Absch.] <= [Filter-End]
			)
		)
	))
)

Thought haven’t tried, another option could be this:

FILTER(
	"inventory",
	(AND(
		OR(
			"Other" = [Source],
			"Other" = [Source2]
		),
		AND(
			[Filter-Start] >= MIN(SORT(UNIQUE(LIST([Datum-1.EK], [Datum-2.EK], [Datum-EK],[Datum-EKF], [Datum-Zuzahlung], [Datum-1.VK], [Datum-2.VK], [Datum-VK], [Datum-Auszahlung], [Datum-Ablöse], [Datum-1.Absch.], [Datum-2.Absch.], [Datum-3.Absch.], [Datum-4.Absch.], [Datum-5.Absch.])),FALSE)),
			[Filter-End] <= MAX(SORT(UNIQUE(LIST([Datum-1.EK], [Datum-2.EK], [Datum-EK],[Datum-EKF], [Datum-Zuzahlung], [Datum-1.VK], [Datum-2.VK], [Datum-VK], [Datum-Auszahlung], [Datum-Ablöse], [Datum-1.Absch.], [Datum-2.Absch.], [Datum-3.Absch.], [Datum-4.Absch.], [Datum-5.Absch.])),FALSE))
		)
	))
)

Sven_Meissner · December 29, 2020, 9:16pm

It gives me data, but not the correct one yet. It does not take into account the columns / source) and (source2) and in some cases no date limitation. I checked all the information.
The second expression doesn’t work. (Arithmetic expression '(([_THISROW-1]. [Filter-Start]> = MIN (SORT (UNIQUE (LIST ([Date-1.EK], [Date-2.EK], [Date-EK], [ Date)))) -EKF], [Date-Co-payment], [Date-1.VK], [Date-2.VK], [Date-VK], [Date-Payment], [Date-Replacement], [ Date-1st dec.], [Date-2nd dec.], [Date-3rd dec.], [Date-4th dec.], [Date-5th dec.])), “FALSE”) )) <= MAX (SORT (UNIQUE (LIST ([Date-1.EC], [Date-2.EC], [Date-EK], [Date-EKF], [Date-Co-payment], [Date-1 .VK], [Date-2.VK], [Date-VK], [Date-Payout], [Date-Replacement], [Date-1.Dep.], [Date-2.Dep.], [Date -3.Absch.], [Date-4.Absch.], [Date-5.Absch.])), “FALSE”))) 'has no valid input types)

LeventK · December 29, 2020, 9:21pm

I am not aware of your table structure, your column structure, where are you trying to set this expression, what data do you have in those source columns etc. Provided you don’t give details, I cannot further comment.

Sven_Meissner · December 29, 2020, 9:45pm

I have to call it a day because it’s very late for us tonight. I’ll try to give you more details tomorrow. Perhaps there is a possibility that I will turn off the app and you will get an in-depth look. I would like to thank you for the good help and your interest.

Sven_Meissner · January 7, 2021, 5:33pm

Hello LeventK. Happy New Year to you.

To my problem. The structure is as follows. Everything is on two tables, “Inventory” and “Filter”. It’s about buying and selling vehicles. The columns [Source] and [Source2] determine whether they already have an invoice or whether they are still being issued. We differentiate between source with “car dealership” and “other”. The columns [Date-1.EC], [Date-2.EC], [Date-EK], [Date-EKF], [Date-copayment], [Date- 1.VK], [Date-2.VK], [Date-VK], [Date-Payout], [Date-Replacement], [Date-1.Dep.], [Date-2.Dep.], [ Date-3rd par.], [Date-4th par.], [Date-5th par.] Are all date columns. The filter that is to sort on the inventory table via Ref has [Filter start], [ Filter-End]. I have an enum on the filter with “still to be edited” and “accounting” differentiates which value of the sources. “Still to edit” should have all data records from which sources “other” and one of the date columns in the Are filter period.
With “accounting” then the same with source “dealership”. The structure is then:

buying: [Source], [Date-1.EK], [Date-2.EK], [Date-EK], [Date-EKF], [Date-additional payment]

selling: [Source2], [Date-1.VK], [Date-2.VK], [Date-VK], [Date-Payout], [Date-Replacement], [Date-1.dec.], [ Date-2.dec.], [Date-3.dec.], [Date-4.dec.], [Date-5.dec.]

Thanks for your nerves

Sven_Meissner · January 7, 2021, 6:18pm

Uploading: C0C19E1C-8697-477F-9868-F60DA7A78822.jpg…

LeventK · January 7, 2021, 6:46pm

@Sven_Meissner
Sorry but your description of the problem is too confusing and I believe I couldn’t be able to follow you either and lost. Thus:

What’s that [Source] and [Source2] column values are? Are they ENUMs? What values they have?

Where is this REF column? Which table? Ref to which table, which column?

A second filter? This ENUM column has 2 values only? {"Still to be edited" , "accounting"}?

Please elaborate

This one is confusing really…Where the choise of selling/buying come from?

Sven_Meissner · January 8, 2021, 8:11pm

sorry for my imprecise language. Unfortunately my application is very extensive with a lot of columns, tables, and data. It would be unbelievable with screenshots. Therefore I try to describe and hope for your understanding. The google translation doesn’t make it clearer.

The filter has an ENUM with the values "still to be processed" and monthly billing "(with which I would like to distinguish the source and source2)
and are located in the “Filters” table.
also “filter start” and “filter end” and “results”, “results” is column type type “list” and element type “REF” with which I access the table “inventory”. All data that are filtered are in the table “Inventory”.

“Inventory” has “Source” and “Source2” and different date columns for payments made. both “sources” are enums with the value “other” and “dealership”. The structure is in the table the source then down payments and final payments, everything for purchasing a car. In the sales area of the car again source2 and down payments and final payment. I would like to achieve this with the filter. (Filter all data records which have the sources “Other” and any date is in the range of the filter date). Or!! (I filter all data records which have the sources “car dealership” and any date in the range of the filter date). I want to make my billing easier at the end of the month, so just find all vehicles that have any payment date in the month.

The suggestions you have made are working, but the date is not precise and not all relevant data records are filtered. If only one date column of the data set falls within the filter time, it should be filtered. We apologize again for the inconvenience. I’ve seen your work (such as the Number2Text app) and look up at you. Brilliant.

Sven_Meissner · January 8, 2021, 8:28pm

Sven_Meissner · January 8, 2021, 8:32pm

Extended filter function of date and condition

Company

Platform

Pricing

Solutions

Support

Account